Oracle - 来自多列的唯一值组合，但返回其他列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5642691/
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 - Unique values combinations from multiple columns, but returning other columns
提问by dscl
I'm trying to figure out how to pull different data scenarios from my table for testing. Assume my table has these three fields FIELD1, FIELD2, FIELD3. If I want to find the various scenarios in my data I could simply do this
我想弄清楚如何从我的表中提取不同的数据场景进行测试。假设我的表有这三个字段FIELD1, FIELD2, FIELD3。如果我想在我的数据中找到各种场景，我可以简单地这样做
select distinct FIELD1, FIELD2, FIELD3 from <table>
And this works. The problem is I have a plethora of other fields I need data from as well, but it doesn't matter what that data is as long as it's associated with the resulting record. So for example if the above query returned this as one of the results
这有效。问题是我还有很多其他字段，我也需要从中获取数据，但只要数据与结果记录相关联，这些数据是什么并不重要。因此，例如，如果上述查询将此作为结果之一返回
FIELD1 FIELD2 FIELD3
----------------------
Y Blue 31
I would want to see the other content fields (4 through 10 lets say). Now wether those came from record number #30 or record #20000 (assuming those records contained those 3 returned values) it doesn't matter as long as all the fields were returned by said record.
我想查看其他内容字段（可以说是 4 到 10 个）。现在，这些是来自记录号 #30 还是记录 #20000（假设这些记录包含这 3 个返回值），只要所有字段都由所述记录返回就无关紧要。
Hope that makes sense and someone can help!
希望这是有道理的，有人可以提供帮助！
回答by Justin Cave
Something like
就像是
SELECT field1,
field2,
field3,
field4,
....
field10
FROM( SELECT field1,
field2,
...,
field10,
rowid rid,
min(rowid) OVER (partition by field1, field2, field3) min_rid
FROM your_table_name )
WHERE rid = min_rid
should work. If there is a primary key, you could use that rather than the ROWID, I'm just using that to have something that is guaranteed to be unique.
应该管用。如果有一个主键，你可以使用它而不是ROWID，我只是用它来保证唯一的东西。
For your curveball
为你的曲线球
SELECT field1,
field2,
field3,
field4,
....
field10
FROM( SELECT field1,
field2,
(CASE WHEN field3 IS NULL
THEN 'NULL'
ELSE field3
END) field3,
...,
field10,
rowid rid,
min(rowid) OVER (partition by field1,
field2,
(CASE WHEN field3 IS NULL
THEN 'NULL'
ELSE field3
END) min_rid
FROM your_table_name )
WHERE rid = min_rid
回答by laurit
Try this:
尝试这个：
select a.*
from tblA a,
(select min(rowid), col_1, col_2 from tblA
group by col_1, col_2) b
where a.rowid=b.rid;
