oracle SQL按不同列选择整行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1463006/
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
SQL Select Entire Row by Distinct Columns
提问by davesbrain
I need an sql statement which will allow me to select an entire row from oracle database, but by distinct columns. Here is a simplified database:
我需要一个 sql 语句，它允许我从 oracle 数据库中选择整行，但按不同的列。这是一个简化的数据库：
Project_Num Title Category
0 Project 1 Admin
0 Project 1 Development
1 Project 2 Admin
2 Project 3 Development
I need my statement to return the following result set:
我需要我的语句返回以下结果集：
0 Project 1 Admin
1 Project 2 Admin
2 Project 3 Development
So each project is returned based on whether its project_num and title are unique. If a project has 2+ entries where its category is different, I need to only select that project once (it doesn't matter which entry I select for that project).
所以每个项目根据其project_num 和title 是否唯一返回。如果一个项目有 2 个以上类别不同的条目，我只需要选择该项目一次（我为该项目选择哪个条目无关紧要）。
Can anyone help me please?
有人可以帮我吗？
回答by Bill Karwin
SELECT Project_Num, Title, MIN(Category) AS Category
FROM MyTable
GROUP BY Project_Num, Title;
回答by Stephen Mesa
Do you even need to have the Category column in your result set?
您甚至需要在结果集中包含 Category 列吗？
If not then you could just use:
如果没有，那么你可以使用：
SELECT DISTINCT Project_Num, Title
FROM MyTable
回答by davesbrain
SELECT *
FROM (SELECT DISTINCT YourDistinctField FROM YourTable) AS A
CROSS APPLY
( SELECT TOP 1 * FROM YourTable B
WHERE B.YourDistinctField = A.YourDistinctField ) AS NewTableName
I have been trying to figure this out for hours now, I tried lots of solutions that didn't work and finally got it by searching for "joining top 1" and adapting that solution I found to a distinct search.
我已经尝试了几个小时来解决这个问题，我尝试了许多不起作用的解决方案，最后通过搜索“加入前 1 名”并将我找到的解决方案调整为不同的搜索来得到它。
