oracle 选择不同和非不同的列值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15075970/
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
Select distinct and non-distinct column values
提问by Baxter
I want to run a query that eliminates duplicate rows based on the values of colA and colB. However, I still want to return the values of colC and colD.
我想运行一个查询，根据 colA 和 colB 的值消除重复行。但是，我仍然想返回 colC 和 colD 的值。
Here is a simple example of what I have so far:
这是我到目前为止所拥有的一个简单示例：
SELECT DISTINCT colA, colB ?colC, colD?
FROM TABLE1
or
或者
SELECT colA, colB ?colC, colD?
FROM TABLE1
GROUP BY colA, colB
Either method does not allow me to return colC, and colD unless I check them as distinct values or group like colA and colB. I do not want to do this, only colA and colB together need to be distinct not colC and colD. I just want to return them.
这两种方法都不允许我返回 colC 和 colD，除非我将它们检查为不同的值或像 colA 和 colB 这样的组。我不想这样做，只有 colA 和 colB 需要区分，而不是 colC 和 colD。我只想归还他们。
Any ideas on how I can accomplish this?
关于我如何做到这一点的任何想法？
回答by Gordon Linoff
Do you want list_agg?
你要list_agg吗？
select colA, colB,
list_agg(distinct colC, ','),
list_agg(distinct colD, ',')
from Table1
Group by ColA, ColB
If any arbitrary value would do for ColC and colD, you can use min():
如果任何任意值都可以用于 ColC 和 colD，您可以使用min()：
select colA, colB, min(colC), min(colD)
from Table1
Group by ColA, ColB
回答by Art
The DISTINCT applies to all values you are selecting, not just to some columns. In your case it will apply to all: colA, colB, colC, colD. It is impossible to select all columns and make some distinct and some not. The only way to do this shown in Gordon's example, this is the only valid example and answer.
DISTINCT 适用于您选择的所有值，而不仅仅是某些列。在您的情况下，它将适用于所有：colA、colB、colC、colD。不可能选择所有列并使某些列不同，而有些则不不同。Gordon 的示例中显示了执行此操作的唯一方法，这是唯一有效的示例和答案。
回答by omilke
Maybe use plain set operations
也许使用简单的集合操作
with UNION and UNION ALL (see http://docs.oracle.com/cd/B19306_01/server.102/b14200/queries004.htm)
使用 UNION 和 UNION ALL（参见http://docs.oracle.com/cd/B19306_01/server.102/b14200/queries004.htm）
UNION colA and colB will eliminate doubled entries in these columns whereas UNION ALL will keep all values from the colC and colD
UNION colA 和 colB 将消除这些列中的重复条目，而 UNION ALL 将保留 colC 和 colD 中的所有值
