Oracle SQL 选择不同
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14805811/
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
Oracle SQL select distinct
提问by
I have a sample table as follows.
我有一个示例表如下。
ID Name Code Address
----+---------------+--------+----------------
1 | Sydney Hall | SH | 11 Abc Street
2 | Sydney Hall | SH | 23 Abc Street
3 | Main Hall | MH | 74 Xyz Street
4 | Odyssey Hall | OH | 133 AbZ Street
5 | Odyssey Hall | OH | 28 Xyx Street
I would like to select distinct Code entries as well as ID and Name for these distinct entries. For the table above I would like to get the following (so I am ignoring the building addresses).
我想为这些不同的条目选择不同的代码条目以及 ID 和名称。对于上面的表格，我想得到以下内容（所以我忽略了建筑物地址）。
ID Name Code
----+---------------+--------+
1 | Sydney Hall | SH
3 | Main Hall | MH
4 | Odyssey Hall | OH
It's probably a Left Join but I can't seem to put it together properly (especially since I'm selecting data from the same table). Does any one have an idea about this? Thanks.
它可能是一个左连接，但我似乎无法将它正确组合在一起（尤其是因为我从同一个表中选择数据）。有没有人对此有任何想法？谢谢。
采纳答案by andy
SELECT *
FROM [table_1]
WHERE [ID] IN (SELECT Min([ID])
FROM [table_1]
GROUP BY CODE
)
回答by Nick.McDermaid
I see everyone has already answered this, but why so complicated?
看到大家已经回答了，为什么这么复杂呢？
SELECT
MIN(ID) ID,
MIN(NAME) NAME,
CODE
FROM TABLE
GROUP BY CODE
回答by Mike Meyers
There are two ways that I would look at doing this. One is to use the FIRSTaggregate function (documented here). The syntax is a little confusing but it should do the job
有两种方法我会考虑这样做。一种是使用FIRST聚合函数（此处记录）。语法有点混乱，但它应该可以完成工作
Select
MIN(ID) keep (dense_rank first order by id) as id,
MIN(NAME) keep (dense_rank first order by id) as name,
CODE
FROM YOUR_TABLE
GROUP BY CODE
The other alternative method that I would suggest is using the ROW_NUMBERfunction as suggested by @techdo, although I think you would need to remove the NAME column from that answer and instead use:
我建议的另一种替代方法是使用ROW_NUMBER@techdo 建议的函数，尽管我认为您需要从该答案中删除 NAME 列，而是使用：
SELECT * FROM(
SELECT
ROW_NUMBER() over (partition by CODE order by ID) RNUM,
ID,
NAME,
CODE
FROM YOUR_TABLE
)x where RNUM=1;
回答by TechDo
Please try:
请尝试：
SELECT * FROM(
SELECT
ROW_NUMBER() over (partition by NAME, CODE order by NAME, CODE) RNUM,
ID,
NAME,
CODE,
ADDRESS
FROM YourTABLE
)x where RNUM=1;
回答by user2001117
You can use this one also:
你也可以使用这个：
SELECT ID, Name, Code
FROM table
WHERE ID IN (SELECT Max(ID)
FROM table
GROUP BY Code
)
