SELECT DISTINCT在多列以及其他列上 [英] SELECT DISTINCT on multiple columns along with other columns

问题描述

我要运行如下查询

select distinct (columnA, columnB, columnC), columnD from MY_TABLE where columnA IS NOT NULL AND columnB IS NOT NULL AND columnC is NOT NULL;

我只想区分columnA，columnB和columnC，而不要on columnD。
但是SQL开发人员在columnA之后指向错误，
我该如何解决？

I only want distinct of columnA, columnB and columnC and NOT ON columnD. But SQL developer is pointing an error right after columnA, How can I fix this?

我尝试使用GROUP BY

I tried to fix my query using GROUP BY

select columnA, columnB, columnC, (select count(*) from TABLE2 WHERE table2columnA = myTable.columnA) from MY_TABLE myTable where columnA IS NOT NULL AND columnB IS NOT NULL AND columnC is NOT NULL GROUP BY columnA, columnB, columnC;

注意我的columnD实际上是另一个选择语句吗？
但这给了我错误

Notice that my columnD is actually another select statement? But this is giving me error

ORA-00979: not a GROUP BY expression
00979. 00000 -  "not a GROUP BY expression"

这不是另一个问题的重复

This is not a duplicate of that another question

推荐答案

SELECT DISTINCT a,b,c FROM t

大致等同于：

SELECT a,b,c FROM t GROUP BY a,b,c,t

习惯使用GROUP BY语法是一个好主意，因为它更强大。
习惯使用GROUP BY语法是一个好主意，
请参见以下帖子：

It's a good idea to get used to the GROUP BY syntax, as it's more powerful. It's a good idea to get used to the GROUP BY syntax, Please see this post:

可能重复吗？

这篇关于SELECT DISTINCT在多列以及其他列上的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！