有一表如下:
columnA columnAB columnC
a1 2 b1
a1 2 b2
a1 2 b3
a1 2 b4
a1 2 b5
b1 0 c1
b2 0 c2
b3 0 c3
b4 0 c4
b5 0 c5
b6 0 c6
a2 2 b1
a2 2 b2
a2 2 b4
a3 2 b1
a3 2 b2
a3 2 b3
a3 2 b4
现有一需求:
1、给N个columnC,同过columnB=0,找到对应的columnA;
2、再通过找到的columnA=columnC和columnB=2,找到columnA;
例如:给定的columnC IN (c1,c2,c3,c4),找到a3。注:columnC的c1,c2,c3,c4,c5,c6可能和b1,b2,b3相等。请问,这个语句该怎么写?先在这谢谢各位了
columnA columnAB columnC
a1 2 b1
a1 2 b2
a1 2 b3
a1 2 b4
a1 2 b5
b1 0 c1
b2 0 c2
b3 0 c3
b4 0 c4
b5 0 c5
b6 0 c6
a2 2 b1
a2 2 b2
a2 2 b4
a3 2 b1
a3 2 b2
a3 2 b3
a3 2 b4
现有一需求:
1、给N个columnC,同过columnB=0,找到对应的columnA;
2、再通过找到的columnA=columnC和columnB=2,找到columnA;
例如:给定的columnC IN (c1,c2,c3,c4),找到a3。注:columnC的c1,c2,c3,c4,c5,c6可能和b1,b2,b3相等。请问,这个语句该怎么写?先在这谢谢各位了
解决方案 »
- 超难的行列转换问题wm_sys.wm_concat中排序的问题,目前只有几分,明日有了再加
- 急!!!!!!!!!!!!! oracle rman 备份
- 快照无法支持主键方式?
- 在ADO.Net中如何实验一个函数同时往两个表中插入数据
- SQL2000 和 orcal 有哪些区别要注意,因为有数据要转??
- 俺想学oracle,但是听说运行他内存至少得1G,不知是真的么?
- 查询时间变化!!!!!!!!
- 问一下关于主建和外建的问题
- @@@@imp80导入数据时出错:
- 怎么才能不让普通用户吧自己的表赋权给别人?
- Warning: Procedure created with compilation errors
- linux下的Oracle安装包?
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
这样不行的,a1,a2,a3全部选出来了,我只要有且只有columnC IN (c1,c2,c3,c4)4个值的
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
group by t.columnB,t.columnC
having count(*)=1 这一句看不懂是什么意思啊
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.columnA
having count(*)=1取出按A,B列分组得到的只有一条记录的啊,从你要的结果来看是这样的
with t as(
select 'a1' columnA, '2' columnB, 'b1' columnC from dual
union all
select 'a1' columnA, '2' columnB, 'b2' columnC from dual
union all
select 'a1' columnA, '2' columnB, 'b3' columnC from dual
union all
select 'a1' columnA, '2' columnB, 'b4' columnC from dual
union all
select 'a1' columnA, '2' columnB, 'b5' columnC from dual
union all
select 'b1' columnA, '0' columnB, 'c1' columnC from dual
union all
select 'b2' columnA, '0' columnB, 'c2' columnC from dual
union all
select 'b3' columnA, '0' columnB, 'c3' columnC from dual
union all
select 'b4' columnA, '0' columnB, 'c4' columnC from dual
union all
select 'a3' columnA, '2' columnB, 'b1' columnC from dual
union all
select 'a3' columnA, '2' columnB, 'b2' columnC from dual
union all
select 'a3' columnA, '2' columnB, 'b3' columnC from dual
union all
select 'a3' columnA, '2' columnB, 'b4' columnC from dual)
select t.columnA
from t,
(select columnA
from t
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.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
这样试试
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晕倒,鼠标动了下,删除了些,应该是上面这个
不行,这样把a1也选出来了,我要的有且只有columnC IN (c1,c2,c3,c4)4个
2、再通过找到的columnA=columnC和columnB=2,找到columnA;
例如:给定的columnC IN (c1,c2,c3,c4),找到a3
你这个例子给的结果不到啊
columnC IN (c1,c2,c3,c4) columnB=0 找到的columnA有b1,b2,b3,b4
在通过 找到的columnA有b1,b2,b3,b4 =columnC 和columnB 找到的columnA有 a1,a2,a3
不会啊,只会找到a3啊,a3对应的columnC刚好等于b1,b2,b3,b4
from lk_123 t这一句,能否帮忙解释下哈
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'))
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 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')
)
--上面少了别名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')
)
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