17,089
社区成员
发帖
与我相关
我的任务
分享
SQL> with tb as(
2 select 'a1' columnA, '2' columnB, 'b1' columnC from dual
3 union all
4 select 'a1' columnA, '2' columnB, 'b2' columnC from dual
5 union all
6 select 'a1' columnA, '2' columnB, 'b3' columnC from dual
7 union all
8 select 'a1' columnA, '2' columnB, 'b4' columnC from dual
9 union all
10 select 'a1' columnA, '2' columnB, 'b5' columnC from dual
11 union all
12 select 'b1' columnA, '0' columnB, 'c1' columnC from dual
13 union all
14 select 'b2' columnA, '0' columnB, 'c2' columnC from dual
15 union all
16 select 'b3' columnA, '0' columnB, 'c3' columnC from dual
17 union all
18 select 'b4' columnA, '0' columnB, 'c4' columnC from dual
19 union all
20 select 'a3' columnA, '2' columnB, 'b1' columnC from dual
21 union all
22 select 'a3' columnA, '2' columnB, 'b2' columnC from dual
23 union all
24 select 'a3' columnA, '2' columnB, 'b3' columnC from dual
25 union all
26 select 'a3' columnA, '2' columnB, 'b4' columnC from dual)
27 select a.columnA from tb a,(select columnA from tb where columnC IN ('c1','c2','c3','c4') and columnB=0) b
28 where a.columnB=2 and a.columnC=b.columnA(+)
29 group by a.columnA
30 having count(*)=4
31 /
COLUMNA
-------
a3
--上面少了别名a:
select a.cola from (
select cola,colb,wm_concat(colc)over(order by colc) colc
from table_name
group by cola,colb
) a
where where a.colb=2
and a.colc=(
select wm_concat(cola)over(order by colc) from table_nale
where colb=0 and colc in('c1','c2','c3','c4')
)
--试试:
select a.cola from (
select cola,colb,wm_concat(colc)over(order by colc) colc
from table_name
group by cola,colb
)
where where a.colb=2
and a.colc=(
select wm_concat(cola)over(order by colc) from table_nale
where colb=0 and colc in('c1','c2','c3','c4')
)
scott@YPCOST> ed
已写入 file afiedt.buf
1 with tb as(
2 select 'a1' columnA,2 columnB,'b1' columnC from dual union all
3 select 'a1', 2, 'b1' from dual union all
4 select 'a1', 2, 'b2' from dual union all
5 select 'a1', 2, 'b3' from dual union all
6 select 'a1', 2, 'b4' from dual union all
7 select 'a1', 2, 'b5' from dual union all
8 select 'b1', 0, 'c1' from dual union all
9 select 'b2', 0, 'c2' from dual union all
10 select 'b3', 0, 'c3' from dual union all
11 select 'b4', 0, 'c4' from dual union all
12 select 'b5', 0, 'c5' from dual union all
13 select 'b6', 0, 'c6' from dual union all
14 select 'a2', 2, 'b1' from dual union all
15 select 'a2', 2, 'b2' from dual union all
16 select 'a2', 2, 'b4' from dual union all
17 select 'a3', 2, 'b1' from dual union all
18 select 'a3', 2, 'b2' from dual union all
19 select 'a3', 2, 'b3' from dual union all
20 select 'a3', 2, 'b4' from dual)
21 select columnA
22 from(select columnA,wm_concat(columnC) over (partition by columnA order by columnC) rn
23 from tb
24 where columnB=2)
25 where rn=
26 (
27 select wm_concat(t1.columnA) from tb t1
28* where t1.columnC in('c1','c2','c3','c4'))
scott@YPCOST> /
CO
--
a3
--试试这个可以不?
select t.columnA,wm_concat(t.columnC)
from tb
where t.columnB=2
group by t.columnA
having wm_concat(columnC)=
(
select wm_concat(t1.columnA) from tb t1
where t1.columnC in('c1','c2','c3','c4'))
select columnA
from (
select columnA,count(*) over (partition by columnB,columnC order by columnA) as cnt
from table t,
(select columnA
from table
where columnB = 0
and columnC in (c1,c2,c3,c4)
) ta
where t.columnB = 2
and t.columnC = ta.columnA
)
where cnt=1
select columnA
from (
select columnA,count(partition by columnB,columnC order by columnA) as cnt
from table t,
(select columnA
from table
where columnB = 0
and columnC in (c1,c2,c3,c4)
) ta
where t.columnB = 2
and t.columnC = ta.columnA
)
where cnt=1
select columnA
from table t,
(select columnA
from table
where columnB = 0
and columnC in (c1,c2,c3,c4)
) ta
where t.columnB = 2
and t.columnC = ta.columnA
group by t.columnB,t.columnC
having count(*)=1
select columnA
from table t,
(select columnA
from table
where columnB = 0
and columnC in (c1,c2,c3,c4)
) ta
where t.columnB = 2
and t.columnC = ta.columnA