Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / RowNumber() / 5 сообщений из 5, страница 1 из 1
26.10.2020, 11:45
    #40011818
unq
unq
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
RowNumber()
Есть запросик. Полная версия выглядит так:
Код: 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
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.
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
1.
row_number() OVER (ORDER BY [Departments] asc ) AS [rn]



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

ЗЫ: надеюсь понятно объяснил. Хотя тут не просто наверное разобраться
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.10.2020, 12:13
    #40011835
unq
unq
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
RowNumber()
unq,

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

Код: 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.
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:23
    #40011840
env
env
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
RowNumber()
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:32
    #40011845
unq
unq
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
RowNumber()
Уже сделал сам, всем сяп)

Код: 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.
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:39
    #40011846
unq
unq
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
RowNumber()
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
| Ответить | Цитировать | Написать  
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / RowNumber() / 5 сообщений из 5, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]