选择中的组编号 (Oracle)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4491052/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Numbering of groups in select (Oracle)
提问by sax
I have a following example - table with name, department and country. I need to create a select statement that lists all records and assigns unique number to each group of department and country (column Group in the example):
我有一个下面的例子 - 带有名称、部门和国家的表。我需要创建一个 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
I thought of some select with analytic function but I found only row_number() over (partition by department, country)which numbers records inside the group and not groups themselves. Do you have any idea how to solve this problem? Thank you!
我想到了一些带有分析功能的选择，但我发现只有row_number() over (partition by department, country)哪些数字记录在组内，而不是组本身。你知道如何解决这个问题吗？谢谢！
回答by Quassnoi
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
or
或者
SELECT t.*, DENSE_RANK() OVER (ORDER BY department desc, country desc) AS grp
FROM mytable
回答by Matthew Watson
Its a bit clunky, but you could do a a sub query ( or in this case using the with clause ) on the table to get the distinct department per country then get the rownum from that.
它有点笨重，但您可以在表上执行子查询（或在本例中使用 with 子句）以获取每个国家/地区的不同部门，然后从中获取行号。
set echo on
DROP TABLE TESTXX
DROP TABLE TESTXX succeeded.
CREATE
TABLE TESTXX
(
NAME VARCHAR2 ( 10 )
, DEPARTMENT VARCHAR2 ( 15 )
, COUNTRY VARCHAR2 ( 2 )
)
CREATE succeeded.
INSERT INTO TESTXX VALUES
( 'James', 'HR', 'UK'
)
1 rows inserted
INSERT INTO TESTXX VALUES
( 'John', 'HR', 'UK'
)
1 rows inserted
INSERT INTO TESTXX VALUES
( 'Alice', 'FI', 'UK'
)
1 rows inserted
INSERT INTO TESTXX VALUES
( 'Bob', 'FI', 'DE'
)
1 rows inserted
INSERT INTO TESTXX VALUES
( 'Frank', 'FI', 'DE'
)
1 rows inserted
.
.
WITH
X AS
(SELECT
XX.*
, ROWNUM R
FROM
(SELECT
DEPARTMENT
, COUNTRY
FROM
TESTXX
GROUP BY
COUNTRY
, DEPARTMENT
ORDER BY
COUNTRY DESC
, DEPARTMENT DESC
) XX
)
SELECT
T.*
, X.R
FROM
TESTXX T
INNER JOIN X
ON
T.DEPARTMENT = X.DEPARTMENT
AND T.COUNTRY = X.COUNTRY
ORDER BY
T.COUNTRY DESC
, T.DEPARTMENT DESC
NAME DEPARTMENT COUNTRY R
---------- --------------- ------- ----------------------
James HR UK 1
John HR UK 1
Alice FI UK 2
Bob FI DE 3
Frank FI DE 3
5 rows selected
