RowNumber() / Microsoft SQL Server

ReSQL.ru

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / RowNumber()

5 сообщений из 5, страница 1 из 1

RowNumber()

#40011818

unq

Участник

Сообщения: 571

Рейтинг: 0 / 0

Есть запросик. Полная версия выглядит так:

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.

SELECT *,
row_number() OVER (ORDER BY #order_by) AS [rn]
FROM
(SELECT
	#if(Normal) {
	[t].[ID]					AS [UserID],
	[pr].[Name]					AS [UserName],
	[pr].[Email],
	[pr].[Position],
	STUFF((
		SELECT N', ' + [r].[Name]
		FROM [RoleUsers] AS [ru] WITH (NOLOCK)
		INNER JOIN [Roles] AS [r] WITH (NOLOCK)
			ON [r].[ID] = [ru].[ID]
			AND [r].[TypeID] = 2
		WHERE [ru].[UserID] = [t].[ID]
		ORDER BY [r].[Name]
		FOR XML PATH, TYPE
	).value(N'.[1]', N'nvarchar(max)'), 1, 2, N'') AS [Departments],
	STUFF((
		SELECT N', ' + [r].[Name]
		FROM [RoleUsers] AS [ru] WITH (NOLOCK)
		INNER JOIN [Roles] AS [r] WITH (NOLOCK)
			ON [r].[ID] = [ru].[ID]
			AND [r].[TypeID] = 0
		WHERE [ru].[UserID] = [t].[ID]
		ORDER BY [r].[Name]
		FOR XML PATH, TYPE
	).value(N'.[1]', N'nvarchar(max)'), 1, 2, N'') AS [StaticRoles]
	} {
	[t].*
	}
FROM (
	SELECT
		#if(Normal) {
		[t].[ID]
		}
		#if(ByDepartment || ByStaticRole) {
		[t].[ID]	AS [RoleID],
		[t].[Name]	AS [RoleName],
		(
			SELECT count(*)
			FROM [RoleUsers] AS [ru] WITH (NOLOCK)
			#if(!ShowHidden || ShowHidden.CriteriaName != "IsTrue") {
			INNER JOIN [Roles] AS [r] WITH (NOLOCK)
				ON [r].[ID] = [ru].[UserID]
			}
			WHERE [ru].[ID] = [t].[ID]
				#param(Name, [ru].[UserName])
				#if(!ShowHidden || ShowHidden.CriteriaName != "IsTrue") {
				AND [r].[Hidden] = 0
				}
				#if(RoleID) {
					#if(RoleID.CriteriaName == "Equality") {
				AND EXISTS (
					SELECT NULL
					FROM [RoleUsers] AS [ru2] WITH (NOLOCK)
					WHERE [ru2].[UserID] = [ru].[UserID]
						#param(RoleID, [ru2].[ID]))
					}

					#if(RoleID.CriteriaName == "NonEquality") {
				AND NOT EXISTS (
					SELECT NULL
					FROM [RoleUsers] AS [ru2] WITH (NOLOCK)
					WHERE [ru2].[UserID] = [ru].[UserID]
						#param(RoleID, [ru2].[ID]))
					}
				}
				#if(!ByDepartment && DepartmentRoleID) {
				AND EXISTS (
					SELECT NULL
					FROM [RoleUsers] AS [ru2] WITH (NOLOCK)
					WHERE [ru2].[UserID] = [ru].[UserID]
						#param(DepartmentRoleID, [ru2].[ID]))
				}
				#if(!ByStaticRole && StaticRoleID) {
				AND EXISTS (
					SELECT NULL
					FROM [RoleUsers] AS [ru2] WITH (NOLOCK)
					WHERE [ru2].[UserID] = [ru].[UserID]
						#param(StaticRoleID, [ru2].[ID]))
				}
		)			AS [cnt],
		CASE WHEN EXISTS (
				SELECT NULL
				FROM [Roles] AS [r] WITH (NOLOCK)
				WHERE [r].[ParentID] = [t].[ID]
					#if(!ShowHidden || ShowHidden.CriteriaName != "IsTrue") {
					AND [r].[Hidden] = 0
					})
			THEN CAST(1 AS bit)
			ELSE CAST(0 AS bit)
		END			AS [HasChildren]
		}
		#if(Count) {
		count(*) AS [cnt]
		}
	FROM [Roles] AS [t] WITH (NOLOCK)
	INNER JOIN [PersonalRoles] AS [pr] WITH (NOLOCK)
		ON [pr].[ID] = [t].[ID]
	WHERE
		#if(ByDepartment || ByStaticRole) {
			-- тип роли [Подразделение] или [Статическая роль] в сабсете
			#if(ByDepartment) { [t].[TypeID] = 2 } { [t].[TypeID] = 0 }
			#param(Role, [t].[ID])
			#param(ParentRoleID, [t].[ParentID])
		} {
			-- тип роли [Сотрудник] в прочих случаях
			[t].[TypeID] = 1
			#param(Name, [t].[Name])
			#if(RoleID) {
				#if(RoleID.CriteriaName == "Equality") {
			AND EXISTS (
				SELECT NULL
				FROM [RoleUsers] AS [ru] WITH (NOLOCK)
				WHERE [ru].[UserID] = [t].[ID]
					#param(RoleID, [ru].[ID]))
				}

				#if(RoleID.CriteriaName == "NonEquality") {
			AND NOT EXISTS (
				SELECT NULL
				FROM [RoleUsers] AS [ru] WITH (NOLOCK)
				WHERE [ru].[UserID] = [t].[ID]
					#param(RoleID, [ru].[ID]))
				}
			}
			#if(DepartmentRoleID) {
			AND EXISTS (
				SELECT NULL
				FROM [RoleUsers] AS [ru] WITH (NOLOCK)
				WHERE [ru].[UserID] = [t].[ID]
					#param(DepartmentRoleID, [ru].[ID]))
			}
			#if(StaticRoleID) {
			AND EXISTS (
				SELECT NULL
				FROM [RoleUsers] AS [ru] WITH (NOLOCK)
				WHERE [ru].[UserID] = [t].[ID]
					#param(StaticRoleID, [ru].[ID]))
			}
			-- если параметр "показать скрытые" не задан или задан как не "да", то не будем отображать скрытые
			#if(!ShowHidden || ShowHidden.CriteriaName != "IsTrue") {
			AND [t].[Hidden] = 0
			}
		}
	) AS [t]
#if(Normal) {
INNER JOIN [PersonalRoles] AS [pr] WITH (NOLOCK)
	ON [pr].[ID] = [t].[ID]
}
#if(ByDepartment || ByStaticRole) {
ORDER BY [t].[RoleName]
}) AS t2
#if(PageOffset) {
WHERE [rn] >= #param(PageOffset) AND [rn] < (#param(PageOffset) + #param(PageLimit))
}
#if(Normal) {
order by #order_by
}

И собственно говоря дебажная, т.е. уже с подставленными параметрами из софта выглядит так

Код: sql

SELECT *,
row_number() OVER (ORDER BY [t2].[UserName] asc ) AS [rn]
FROM
(SELECT
	
	[t].[ID]					AS [UserID],
	[pr].[Name]					AS [UserName],
	[pr].[Email],
	[pr].[Position],
	STUFF((
		SELECT N', ' + [r].[Name]
		FROM [RoleUsers] AS [ru] WITH (NOLOCK)
		INNER JOIN [Roles] AS [r] WITH (NOLOCK)
			ON [r].[ID] = [ru].[ID]
			AND [r].[TypeID] = 2
		WHERE [ru].[UserID] = [t].[ID]
		ORDER BY [r].[Name]
		FOR XML PATH, TYPE
	).value(N'.[1]', N'nvarchar(max)'), 1, 2, N'') AS [Departments],
	STUFF((
		SELECT N', ' + [r].[Name]
		FROM [RoleUsers] AS [ru] WITH (NOLOCK)
		INNER JOIN [Roles] AS [r] WITH (NOLOCK)
			ON [r].[ID] = [ru].[ID]
			AND [r].[TypeID] = 0
		WHERE [ru].[UserID] = [t].[ID]
		ORDER BY [r].[Name]
		FOR XML PATH, TYPE
	).value(N'.[1]', N'nvarchar(max)'), 1, 2, N'') AS [StaticRoles]
	
FROM (
	SELECT
		
		[t].[ID]
		
	FROM [Roles] AS [t] WITH (NOLOCK)
	INNER JOIN [PersonalRoles] AS [pr] WITH (NOLOCK)
		ON [pr].[ID] = [t].[ID]
	WHERE
		
			-- тип роли [Сотрудник] в прочих случаях
			[t].[TypeID] = 1
			
			
			-- если параметр "показать скрытые" не задан или задан как не "да", то не будем отображать скрытые
			
			AND [t].[Hidden] = 0
			
	) AS [t]

INNER JOIN [PersonalRoles] AS [pr] WITH (NOLOCK)
	ON [pr].[ID] = [t].[ID]
) AS t2

WHERE [rn] >= 1 AND [rn] < (1 + 20)

order by [t2].[UserName] asc;
GO

Не могу заставить отработать функцию RowNumber. Я естественно знаю что нужно чтобы она отработала, но суть в другом мне нужно чтобы она располагалась не внутри подзапроса а снаружи. Объясню почему я к этому пришел. Есть в подзапросе колонки которые компануются через STUFF() а мне по этим двум колонкам нужна сортировка.

Я не могу написать строку вида

Код: sql

row_number() OVER (ORDER BY [Departments] asc ) AS [rn]

как пронумировать колонки Departments и StaticRoles ?

ЗЫ: надеюсь понятно объяснил. Хотя тут не просто наверное разобраться

...

Рейтинг:

0 / 0

26.10.2020, 11:45

| Ответить | Цитировать | Написать

RowNumber()

#40011835

unq

Участник

Сообщения: 571

Рейтинг: 0 / 0

unq,

Перекомпоновал немного, но могу применить сортировку((

Код: sql

SELECT
	
	[t].[ID]					AS [UserID],
	[pr].[Name]					AS [UserName],
	[pr].[Email],
	[pr].[Position],
	[t].[Departments],
	[t].[StaticRoles]
	
FROM (
	SELECT
		
		[t].[ID],
	STUFF((
		SELECT N', ' + [r].[Name]
		FROM [RoleUsers] AS [ru] WITH (NOLOCK)
		INNER JOIN [Roles] AS [r] WITH (NOLOCK)
			ON [r].[ID] = [ru].[ID]
			AND [r].[TypeID] = 2
		WHERE [ru].[UserID] = [t].[ID]
		ORDER BY [r].[Name]
		FOR XML PATH, TYPE
	).value(N'.[1]', N'nvarchar(max)'), 1, 2, N'') AS [Departments],
	STUFF((
		SELECT N', ' + [r].[Name]
		FROM [RoleUsers] AS [ru] WITH (NOLOCK)
		INNER JOIN [Roles] AS [r] WITH (NOLOCK)
			ON [r].[ID] = [ru].[ID]
			AND [r].[TypeID] = 0
		WHERE [ru].[UserID] = [t].[ID]
		ORDER BY [r].[Name]
		FOR XML PATH, TYPE
	).value(N'.[1]', N'nvarchar(max)'), 1, 2, N'') AS [StaticRoles],
		row_number() OVER (ORDER BY [pr].[Name] asc , [Departments] desc ) AS [rn]
		
	FROM [Roles] AS [t] WITH (NOLOCK)
	INNER JOIN [PersonalRoles] AS [pr] WITH (NOLOCK)
		ON [pr].[ID] = [t].[ID]
	WHERE
		
			-- тип роли [Сотрудник] в прочих случаях
			[t].[TypeID] = 1
			
			
			-- если параметр "показать скрытые" не задан или задан как не "да", то не будем отображать скрытые
			
			AND [t].[Hidden] = 0
			
	) AS [t]

INNER JOIN [PersonalRoles] AS [pr] WITH (NOLOCK)
	ON [pr].[ID] = [t].[ID]

WHERE [rn] >= 1 AND [rn] < (1 + 20)

order by [pr].[Name] asc , [Departments] desc;
GO

...

Рейтинг:

0 / 0

26.10.2020, 12:13

| Ответить | Цитировать | Написать

RowNumber()

#40011840

env

Участник

Откуда: Россия, Москва

Сообщения: 6 692

Рейтинг: 0 / 0

unq,

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.

with data as 
(
 ...
 --выборка и вычисления
),
ordered as (
select
    data.*,
    row_number() over (...) rn
...
)
select
    ....
from
    ordered
where
    rn >= ..
order by ...

Чем не устраивает?

...

Рейтинг:

0 / 0

26.10.2020, 12:23

| Ответить | Цитировать | Написать

RowNumber()

#40011845

unq

Участник

Сообщения: 571

Рейтинг: 0 / 0

Уже сделал сам, всем сяп)

Код: sql

env,
SELECT *
FROM
(SELECT

	[t].[ID]					AS [UserID],
	[pr].[Name]					AS [UserName],
	[pr].[Email],
	[pr].[Position],
	[t].[Departments],
	[t].[StaticRoles],
	row_number() OVER (ORDER BY [pr].[Name] asc , [Departments] desc ) AS [rn]

FROM (
	SELECT

		[t].[ID],
		STUFF((
		SELECT N', ' + [r].[Name]
		FROM [RoleUsers] AS [ru] WITH (NOLOCK)
		INNER JOIN [Roles] AS [r] WITH (NOLOCK)
			ON [r].[ID] = [ru].[ID]
			AND [r].[TypeID] = 2
		WHERE [ru].[UserID] = [t].[ID]
		ORDER BY [r].[Name]
		FOR XML PATH, TYPE
	).value(N'.[1]', N'nvarchar(max)'), 1, 2, N'') AS [Departments],
	STUFF((
		SELECT N', ' + [r].[Name]
		FROM [RoleUsers] AS [ru] WITH (NOLOCK)
		INNER JOIN [Roles] AS [r] WITH (NOLOCK)
			ON [r].[ID] = [ru].[ID]
			AND [r].[TypeID] = 0
		WHERE [ru].[UserID] = [t].[ID]
		ORDER BY [r].[Name]
		FOR XML PATH, TYPE
	).value(N'.[1]', N'nvarchar(max)'), 1, 2, N'') AS [StaticRoles]

	FROM [Roles] AS [t] WITH (NOLOCK)
	INNER JOIN [PersonalRoles] AS [pr] WITH (NOLOCK)
		ON [pr].[ID] = [t].[ID]
	WHERE

			-- тип роли [Сотрудник] в прочих случаях
			[t].[TypeID] = 1


			-- если параметр "показать скрытые" не задан или задан как не "да", то не будем отображать скрытые

			AND [t].[Hidden] = 0

	) AS [t]

INNER JOIN [PersonalRoles] AS [pr] WITH (NOLOCK)
	ON [pr].[ID] = [t].[ID]

) AS [t2]

WHERE [t2].[rn] >= 1 AND [t2].[rn] < (1 + 20)

ORDER BY [t2].[rn];
GO

...

Рейтинг:

0 / 0

26.10.2020, 12:32

| Ответить | Цитировать | Написать

RowNumber()

#40011846

unq

Участник

Сообщения: 571

Рейтинг: 0 / 0

env

unq,

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.

with data as 
(
 ...
 --выборка и вычисления
),
ordered as (
select
    data.*,
    row_number() over (...) rn
...
)
select
    ....
from
    ordered
where
    rn >= ..
order by ...

Чем не устраивает?

Спасибо за вариант!

...

Рейтинг:

0 / 0

26.10.2020, 12:39

| Ответить | Цитировать | Написать

5 сообщений из 5, страница 1 из 1

Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / RowNumber()

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=46&gotonew=1&tid=1685494]:	0ms
get settings:	8ms
get forum list:	12ms
check forum access:	4ms
check topic access:	4ms
track hit:	30ms
get topic data:	6ms
get first new msg:	6ms
get forum data:	2ms
get page messages:	38ms
get tp. blocked users:	1ms
others:	11ms

total:	122ms