RT
例如with t1 as
(select '1' id, '2' num from dual union all
select '1' id, '3' num from dual union all
select '1' id, '5' num from dual union all
select '2' id, '4' num from dual union all
select '2' id, '25' num from dual union all
select '2' id, '5' num from dual union all
select '2' id, '7' num from dual union all
select '3' id, '2' num from dual union all
select '3' id, '3' num from dual union all
select '3' id, '1' num from dual union all
select '4' id, '6' num from dual union all
select '4' id, '8' num from dual )
我求每个id中num>4的num个数,如果代码是这样的话
select id,count(0) from t1 where num>4
group by id
id为3的自动不显示了,我想让id为3的结果显示为0,如何实现啊?
例如with t1 as
(select '1' id, '2' num from dual union all
select '1' id, '3' num from dual union all
select '1' id, '5' num from dual union all
select '2' id, '4' num from dual union all
select '2' id, '25' num from dual union all
select '2' id, '5' num from dual union all
select '2' id, '7' num from dual union all
select '3' id, '2' num from dual union all
select '3' id, '3' num from dual union all
select '3' id, '1' num from dual union all
select '4' id, '6' num from dual union all
select '4' id, '8' num from dual )
我求每个id中num>4的num个数,如果代码是这样的话
select id,count(0) from t1 where num>4
group by id
id为3的自动不显示了,我想让id为3的结果显示为0,如何实现啊?
Connected to Oracle9i Enterprise Edition Release 9.2.0.1.0
Connected as billing
SQL>
SQL> with t1 as
2 (select '1' id, '2' num from dual union all
3 select '1' id, '3' num from dual union all
4 select '1' id, '5' num from dual union all
5 select '2' id, '4' num from dual union all
6 select '2' id, '25' num from dual union all
7 select '2' id, '5' num from dual union all
8 select '2' id, '7' num from dual union all
9 select '3' id, '2' num from dual union all
10 select '3' id, '3' num from dual union all
11 select '3' id, '1' num from dual union all
12 select '4' id, '6' num from dual union all
13 select '4' id, '8' num from dual )
14 select id,count(0) from t1
15 group by id having count(0) >4
16 ;ID COUNT(0)
-- ----------SQL
---应该是这样。
Connected to Oracle9i Enterprise Edition Release 9.2.0.1.0
Connected as billing
SQL>
SQL> with t1 as
2 (select '1' id, '2' num from dual union all
3 select '1' id, '3' num from dual union all
4 select '1' id, '5' num from dual union all
5 select '2' id, '4' num from dual union all
6 select '2' id, '25' num from dual union all
7 select '2' id, '5' num from dual union all
8 select '2' id, '7' num from dual union all
9 select '3' id, '2' num from dual union all
10 select '3' id, '3' num from dual union all
11 select '3' id, '1' num from dual union all
12 select '4' id, '6' num from dual union all
13 select '4' id, '8' num from dual )
14 select id,decode(id,3,0,count(0)) from t1 where num>4
15 group by id
16 ;ID DECODE(ID,3,0,COUNT(0))
-- -----------------------
1 1
2 3
4 2SQL>
ID count(0)
1 1
2 3
3 0
4 2
group by id
with t1 as
(select '1' id, '2' num from dual union all
select '1' id, '3' num from dual union all
select '1' id, '5' num from dual union all
select '2' id, '4' num from dual union all
select '2' id, '25' num from dual union all
select '2' id, '5' num from dual union all
select '2' id, '7' num from dual union all
select '3' id, '2' num from dual union all
select '3' id, '3' num from dual union all
select '3' id, '1' num from dual union all
select '4' id, '6' num from dual union all
select '4' id, '8' num from dual )
,t2 AS(
select id,count(0)cnt from t1 where num>4
group by id
)
SELECT DISTINCT a.id,Nvl(b.cnt,0) FROM t1 a,t2 b
WHERE a.id=b.id(+);ID CNT
----------
1 1
2 3
3 0
4 2