有A表如下：
ID KEY NEW
1 1000 0
2 1000 0
3 1000 0
4 1001 0
5 1001 1
6 1002 1
7 1768 0
8 1768 0
9 1768 1
10 2009 0
11 2009 0
12 2345 0
如上表，需要列出：KEY相同，并且NEW全部为0的记录，如下
KEY COUNT(KEY) NEW
1000 3 0
2009 2 0
2345 1 0
select KEY , COUNT(KEY),sum(NEW) NEW
from test
group by key
having sum(NEW)=0;
我来写一个
SQL> select key,ct,'0' new
from
(select distinct key,count(*) over(partition by key) ct,
sum(new) over(partition by key) sm
from test)
where sm=0;
KEY CT N
---------- ---------- -
1000 3 0
2009 2 0
2345 1 0
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/21220558/viewspace-590945/ ，如需转载，请注明出处，否则将追究法律责任。
