|
Помогите с запросом
#39679917
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
Ссылка на профиль пользователя:
|
Участник
Откуда: Москва
Сообщения: 9 956
|
|
Вариант 1 1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
SELECT
Employee.FirstName
,Employee.LastName
,AdditionalFieldStringValues.Value
,EmployeeGroup.Name
,Employee.SecondName
,min(case when k.rn = 1 then k.AcsKeyId end) as AcsKeyId1
,min(case when k.rn = 2 then k.AcsKeyId end) as AcsKeyId2
FROM dbo.AdditionalFieldStringValues
INNER JOIN dbo.Employee
ON AdditionalFieldStringValues.EmployeeId = Employee._id
INNER JOIN dbo.EmployeeGroup
ON Employee.EmployeeGroupID = EmployeeGroup._id
outer apply (select top (2) AcsKeyId, row_number() over (order by (select 1)) from dbo.AcsKey2EmployeeAssignment where EmployeeId = Employee._id and AssignmentModificationType = 0) k(AcsKeyId, rn)
WHERE Employee.IsRemoved = 0
group by
Employee.FirstName
,Employee.LastName
,AdditionalFieldStringValues.Value
,EmployeeGroup.Name
,Employee.SecondName
Вариант 2 1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
SELECT
Employee.FirstName
,Employee.LastName
,AdditionalFieldStringValues.Value
,EmployeeGroup.Name
,Employee.SecondName
,k1.AcsKeyId1
,k2.AcsKeyId2
FROM dbo.AdditionalFieldStringValues
INNER JOIN dbo.Employee
ON AdditionalFieldStringValues.EmployeeId = Employee._id
INNER JOIN dbo.EmployeeGroup
ON Employee.EmployeeGroupID = EmployeeGroup._id
outer apply (select top (1) AcsKeyId from dbo.AcsKey2EmployeeAssignment where EmployeeId = Employee._id and AssignmentModificationType = 0) k1(AcsKeyId1)
outer apply (select top (1) AcsKeyId from dbo.AcsKey2EmployeeAssignment where EmployeeId = Employee._id and AssignmentModificationType = 0 and AcsKeyId <> k1.AcsKeyId1) k2(AcsKeyId2)
WHERE Employee.IsRemoved = 0
Вариант 3 1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
SELECT
Employee.FirstName
,Employee.LastName
,AdditionalFieldStringValues.Value
,EmployeeGroup.Name
,Employee.SecondName
,k.x.value('key[1]/@AcsKeyId', 'int') as AcsKeyId1
,k.x.value('key[2]/@AcsKeyId', 'int') as AcsKeyId2
FROM dbo.AdditionalFieldStringValues
INNER JOIN dbo.Employee
ON AdditionalFieldStringValues.EmployeeId = Employee._id
INNER JOIN dbo.EmployeeGroup
ON Employee.EmployeeGroupID = EmployeeGroup._id
outer apply (select top (2) AcsKeyId as [@AcsKeyId] from dbo.AcsKey2EmployeeAssignment where EmployeeId = Employee._id and AssignmentModificationType = 0 for xml path('key'), type) k(x)
WHERE Employee.IsRemoved = 0
|
|
|
