表t
id num
1 11
1 12
1 100
2 13
2 14
2 111select sum(case when t.num >= 10 then 1 else 0 end) c1,
sum(case when t.num >= 100 then 1 else 0 end) c2
from t
我是想统计一下,num>=10时有多少个id,num>=100时有多少个id
上面的写法没办法过滤掉重复的ID,
想要过滤重复,要怎么处理呢?
id num
1 11
1 12
1 100
2 13
2 14
2 111select sum(case when t.num >= 10 then 1 else 0 end) c1,
sum(case when t.num >= 100 then 1 else 0 end) c2
from t
我是想统计一下,num>=10时有多少个id,num>=100时有多少个id
上面的写法没办法过滤掉重复的ID,
想要过滤重复,要怎么处理呢?
select sum(case when c1>0 then 1 else then 0 end) "大于10",
sum(case when c2>0 then 1 else then 0 end) "大于100"
from (select sum(case when t.num >= 10 then 1 else 0 end) c1,
sum(case when t.num >= 100 then 1 else 0 end) c2
from t
group by id)
select sum(max(case when t.num >= 10 then 1 else 0 end)) c1,
sum(max(case when t.num >= 100 then 1 else 0 end)) c2
from t group by id
id num
1 11
1 12
1 100
2 13
2 14
2 111
3 15
3 16我是想统计一下,num>=10时有多少个id,num>=100时有多少个id
像这样的数据
统计的结果应该是
num>=10时,id的数量有3个;
num>=100时,id的数量有2个
SELECT COUNT(DISTINCT CASE WHEN NUM>=10 THEN ID END),
COUNT(DISTINCT CASE WHEN NUM>=100 THEN ID END),
COUNT(DISTINCT CASE WHEN NUM>=1000 THEN ID END)
FROM t
用count可不统计值为null的目的,同时distinct达到了唯一计算的目的。
count(distinct decode(sign(100-num),-1,id)),
from a
SELECT 1 id,11 num FROM dual
UNION ALL
SELECT 1,12 FROM dual
UNION ALL
SELECT 1,100 FROM dual
UNION ALL
SELECT 2,13 FROM dual
UNION ALL
SELECT 2,14 FROM dual
UNION ALL
SELECT 2,111 FROM dual
UNION ALL
SELECT 3,15 FROM dual
UNION ALL
SELECT 3,16 FROM dual
)
select sum(max(case when t.num >= 10 then 1 else 0 end)) 大于10的id个数,
sum(max(case when t.num >= 100 then 1 else 0 end)) 大于100的id个数
from t group by id
结果:
大于10的ID个数 大于100的ID个数
----------------------------
3 2
SQL> edi
已写入 file afiedt.buf 1 WITH temp AS(
2 SELECT 1 id,11 num FROM dual
3 UNION ALL
4 SELECT 1,12 FROM dual
5 UNION ALL
6 SELECT 1,100 FROM dual
7 UNION ALL
8 SELECT 2,13 FROM dual
9 UNION ALL
10 SELECT 2,14 FROM dual
11 UNION ALL
12 SELECT 2,111 FROM dual
13 UNION ALL
14 SELECT 3,15 FROM dual
15 UNION ALL
16 SELECT 3,16 FROM dual
17 )
18 select count(distinct case when num>=10 then id end),
19 count(distinct case when num>=100 then id end)
20* from temp
SQL> /COUNT(DISTINCTCASEWHENNUM>=10THENIDEND) COUNT(DISTINCTCASEWHENNUM>=100THENIDEND)
--------------------------------------- ----------------------------------------
3 2