我有以下查询来获取员工的不同国籍:
SELECT
an.EmployeeId,c.CountryId,
ROW_NUMBER() OVER(PARTITION BY an.EmployeeId ORDER BY an.EmployeeId) AS rn
FROM
Nationality an
JOIN
country c ON an.CountryId = c.id
返回以下结果:
EmployeeId CountryId rn
-----------------------
45 1 1
45 26 2
33 45 1
66 90 1
我的预期输出如下,我希望它没有row_number()
:
EmployeeId Count
----------------
45 2
33 1
66 1
您不需要加入国家/地区表
您可以简单地使用下面的查询。
select EmployeeId, Count(CountryID)
From Nationality
Group By EmployeeId
您的查询应该如下所示:
SELECT an.EmployeeId ,count(c.ID) AS nationalitycount
FROM AF_Nationality an
JOIN country c ON an.CountryId = c.id
GROUP BY an.EmployeeId