我有一个以下示例 – 包含姓名,部门和国家/地区的表格.我需要创建一个select语句,列出所有记录,并为每个部门和国家/地区分配唯一编号(示例中的列组)：
Name Department Country Group ====== ============ ========= ===== James HR UK 1 John HR UK 1 Alice Finance UK 2 Bob Finance DE 3 Frank Finance DE 3
我想到了一些具有分析功能的select,但我发现只有row_number()over(按部门划分,国家/地区),它在组内编号记录而不是组本身.你知道如何解决这个问题吗？谢谢！
解决方法
SELECT t.*,q.grp
FROM (
SELECT q.*,rownum AS grp
FROM (
SELECT DISTINCT department,country
FROM mytable
ORDER BY
department,country
) q
) q
JOIN mytable t
ON t.department = q.department
AND t.country = q.country
要么
SELECT t.*,DENSE_RANK() OVER (ORDER BY department desc,country desc) AS grp FROM mytable
