Try it .. SQL> select * 2 from A tt; NBM SYSL XSE ---------- ---- ---------- 1 4% 120 1 6% 20 1 4% 80 2 6% 100 2 4% 200 2 6% 60 3 4% 50 3 4% 30 3 4% 80 4 4% 10 4 4% 8011 rows selectedSQL> SQL> select decode(sign(sysl_6), 2 1,decode(sign(sum_xse-180),1,nbm,null), 3 0,decode(sign(sum_xse-100),1,nbm,null), 4 null) as nbm 5 from ( 6 select nbm, 7 sum(decode(sysl,'4%',1,0)) as sysl_4, 8 sum(decode(sysl,'6%',1,0)) as sysl_6, 9 count(1) as counts, 10 sum(xse) as sum_xse 11 from A tt 12 group by nbm 13 )zz; NBM ---------- 1 2 3
select nbm from A where 180 < (select sum(xse) from A where sysl = 6%) union all select nbm from A where 100 < (select sum(xse) from A where sysl = 4%)
LOVE 野,你大概还没太理解我的意思。 select nbm from A where 180 < (select sum(xse) from A where sysl = 6%) 你这个查询结果估计要么是空要么是全部是nbm。你觉得呢?
SQL> select *
2 from A tt; NBM SYSL XSE
---------- ---- ----------
1 4% 120
1 6% 20
1 4% 80
2 6% 100
2 4% 200
2 6% 60
3 4% 50
3 4% 30
3 4% 80
4 4% 10
4 4% 8011 rows selectedSQL>
SQL> select decode(sign(sysl_6),
2 1,decode(sign(sum_xse-180),1,nbm,null),
3 0,decode(sign(sum_xse-100),1,nbm,null),
4 null) as nbm
5 from (
6 select nbm,
7 sum(decode(sysl,'4%',1,0)) as sysl_4,
8 sum(decode(sysl,'6%',1,0)) as sysl_6,
9 count(1) as counts,
10 sum(xse) as sum_xse
11 from A tt
12 group by nbm
13 )zz; NBM
----------
1
2
3
from A
where 180 < (select sum(xse)
from A
where sysl = 6%)
union all
select nbm
from A
where 100 < (select sum(xse)
from A
where sysl = 4%)
select nbm
from A
where 180 < (select sum(xse)
from A
where sysl = 6%)
你这个查询结果估计要么是空要么是全部是nbm。你觉得呢?