--这种结果? SQL> WITH aa AS( 2 SELECT '1' id, 'a' cls, 600 val FROM dual 3 UNION ALL 4 SELECT '2' , 'b' , 320 FROM dual 5 union all 6 SELECT '3' , 'c' , 310 FROM dual 7 ), 8 bb AS( 9 SELECT '1' id, 'b' cls, 78 val FROM dual 10 UNION ALL 11 SELECT '2' , 'a' , 533 FROM dual 12 union all 13 SELECT '1' , 'a' , 100 FROM dual 14 union all 15 SELECT '3' , 'a' , 323 FROM dual 16 union all 17 SELECT '4' , 'b' , 400 FROM dual 18 union all 19 SELECT '5' , 'a' , 345 FROM dual 20 union all 21 SELECT '5' , 'b' , 213 FROM dual 22 union all 23 SELECT '6' , 'a' , 438 FROM dual 24 ) 25 select nvl(a.id,b.id) id,nvl(a.cls,b.cls) cls,a.VAL_aa,b.VAL_bb from 26 (select id,min(cls) cls,sum(val) VAL_aa from aa group by id having sum(val)>=500) a 27 full join 28 (select id,min(cls) cls,sum(val) VAL_bb from bb group by id having sum(val)>=500) b 29 on a.id=b.id 30 order by id;ID CL VAL_AA VAL_BB -- -- ---------- ---------- 1 a 600 2 a 533 5 a 558
不对,我想得到的结果是如下,结果表要包含所有满足条件的原始数据。ID CLS VAL_aa VAL_bb 1 a 600 100 1 b 78 2 a 533 2 b 320 5 a 345 5 b 213
WITH aa AS( SELECT '1' id, 'a' cls, 600 val FROM dual UNION ALL SELECT '2' , 'b' , 320 FROM dual union all SELECT '3' , 'c' , 310 FROM dual ), bb AS( SELECT '1' id, 'b' cls, 78 val FROM dual UNION ALL SELECT '2' , 'a' , 533 FROM dual union all SELECT '1' , 'a' , 100 FROM dual union all SELECT '3' , 'a' , 323 FROM dual union all SELECT '4' , 'b' , 400 FROM dual union all SELECT '5' , 'a' , 345 FROM dual union all SELECT '5' , 'b' , 213 FROM dual union all SELECT '6' , 'a' , 438 FROM dual ) select nvl(e.id,f.id) id,nvl(e.cls,f.cls) cls,val_aa,val_bb from ( select a.id, a.cls, a.val val_aa from aa a, (select id,sum(val) sum_val from aa group by id having sum(val)>=500 union select id,sum(val) sum_val from bb group by id having sum(val)>=500 ) c where c.id=a.id) e full join ( select b.id, b.cls, b.val val_bb from bb b, (select id,sum(val) sum_val from aa group by id having sum(val)>=500 union select id,sum(val) sum_val from bb group by id having sum(val)>=500 ) c where c.id=b.id)f on e.id=f.id and e.cls=f.cls order by 1,2 ID CLS VAL_AA VAL_BB 1 a 600 100 1 b 78 2 a 533 2 b 320 5 a 345 5 b 213
select aa.ID, aa.cls, aa.val, dd.val from aa inner join (select id,cls, val from bb) dd on dd.id = aa.id and dd.cls = aa.cls inner join (select ID, cls, sum(val) from (select * from aa union all select * from bb) group by ID, cls having by sum(val) > 500) cc on cc.id = aa.id and cc.val = aa.val
with t as (select id from aa group by id having sum(val)>=500 union all select id from bb group by id having sum(val)>=500) select (case when k.id is null then j.id else k.id end), (case when k.cls is null then j.cls else k.cls end),k.val,j.val from (select * from aa where exists(select 1 from t where t.id=aa.id)) k full join (select * from bb where exists(select 1 from t where t.id=bb.id)) j on k.id=j.id and k.cls=j.cls
--这种结果?
SQL> WITH aa AS(
2 SELECT '1' id, 'a' cls, 600 val FROM dual
3 UNION ALL
4 SELECT '2' , 'b' , 320 FROM dual
5 union all
6 SELECT '3' , 'c' , 310 FROM dual
7 ),
8 bb AS(
9 SELECT '1' id, 'b' cls, 78 val FROM dual
10 UNION ALL
11 SELECT '2' , 'a' , 533 FROM dual
12 union all
13 SELECT '1' , 'a' , 100 FROM dual
14 union all
15 SELECT '3' , 'a' , 323 FROM dual
16 union all
17 SELECT '4' , 'b' , 400 FROM dual
18 union all
19 SELECT '5' , 'a' , 345 FROM dual
20 union all
21 SELECT '5' , 'b' , 213 FROM dual
22 union all
23 SELECT '6' , 'a' , 438 FROM dual
24 )
25 select nvl(a.id,b.id) id,nvl(a.cls,b.cls) cls,a.VAL_aa,b.VAL_bb from
26 (select id,min(cls) cls,sum(val) VAL_aa from aa group by id having sum(val)>=500) a
27 full join
28 (select id,min(cls) cls,sum(val) VAL_bb from bb group by id having sum(val)>=500) b
29 on a.id=b.id
30 order by id;ID CL VAL_AA VAL_BB
-- -- ---------- ----------
1 a 600
2 a 533
5 a 558
1 a 600 100
1 b 78
2 a 533
2 b 320
5 a 345
5 b 213
SELECT '1' id, 'a' cls, 600 val FROM dual
UNION ALL
SELECT '2' , 'b' , 320 FROM dual
union all
SELECT '3' , 'c' , 310 FROM dual
),
bb AS(
SELECT '1' id, 'b' cls, 78 val FROM dual
UNION ALL
SELECT '2' , 'a' , 533 FROM dual
union all
SELECT '1' , 'a' , 100 FROM dual
union all
SELECT '3' , 'a' , 323 FROM dual
union all
SELECT '4' , 'b' , 400 FROM dual
union all
SELECT '5' , 'a' , 345 FROM dual
union all
SELECT '5' , 'b' , 213 FROM dual
union all
SELECT '6' , 'a' , 438 FROM dual
)
select nvl(e.id,f.id) id,nvl(e.cls,f.cls) cls,val_aa,val_bb from (
select a.id, a.cls, a.val val_aa
from aa a,
(select id,sum(val) sum_val
from aa
group by id
having sum(val)>=500
union
select id,sum(val) sum_val
from bb
group by id
having sum(val)>=500
) c
where c.id=a.id) e full join
(
select b.id, b.cls, b.val val_bb
from bb b,
(select id,sum(val) sum_val
from aa
group by id
having sum(val)>=500
union
select id,sum(val) sum_val
from bb
group by id
having sum(val)>=500
) c
where c.id=b.id)f
on e.id=f.id
and e.cls=f.cls
order by 1,2
ID CLS VAL_AA VAL_BB
1 a 600 100
1 b 78
2 a 533
2 b 320
5 a 345
5 b 213
from aa
inner join (select id,cls, val from bb) dd on dd.id = aa.id
and dd.cls = aa.cls
inner join (select ID, cls, sum(val)
from (select *
from aa
union all
select * from bb)
group by ID, cls
having by sum(val) > 500) cc on cc.id = aa.id
and cc.val = aa.val
(select id from aa group by id having sum(val)>=500
union all
select id from bb group by id having sum(val)>=500)
select (case when k.id is null then j.id else k.id end),
(case when k.cls is null then j.cls else k.cls end),k.val,j.val from (select * from aa where exists(select 1 from t where t.id=aa.id)) k
full join (select * from bb where exists(select 1 from t where t.id=bb.id)) j on k.id=j.id and k.cls=j.cls