表数据为:
A B type
name1 address 1
name1 address 2
name1 address 3
name1 address 4要查询出满足 type = 1 ,type = 2,type = 3,type = 4
的这条数据:
name1 address
A B type
name1 address 1
name1 address 2
name1 address 3
name1 address 4要查询出满足 type = 1 ,type = 2,type = 3,type = 4
的这条数据:
name1 address
必须同时满足所有条件,就有些麻烦了。 建议思路, 滤重 -> 行转列 -> 过滤
例 name1 address 1
name1 address 1
name1 address 2
name1 address 3
name1 address 4
->
name1 address 1
name1 address 2
name1 address 3
name1 address 4
->
name1 address 1,2,3,4
->
where type = '1,2,3,4'
select a, b from
(select a,b from test t where exists
(select * from test t1 where t1.a=t.a and t1.b=t.b and t1.type=1) and exists
(select * from test t1 where t1.a=t.a and t1.b=t.b and t1.type=2) and exists
(select * from test t1 where t1.a=t.a and t1.b=t.b and t1.type=3) and exists
(select * from test t1 where t1.a=t.a and t1.b=t.b and t1.type=4))
group by a,b;
[/code]
(select a,b from test t where exists
(select * from test t1 where t1.a=t.a and t1.b=t.b and t1.type=1) and exists
(select * from test t1 where t1.a=t.a and t1.b=t.b and t1.type=2) and exists
(select * from test t1 where t1.a=t.a and t1.b=t.b and t1.type=3) and exists
(select * from test t1 where t1.a=t.a and t1.b=t.b and t1.type=4))
group by a,b;
也是一种办法,但效率很低。 同一张表,需要查询 4 遍。
另外,不能写 select * from ,应该是 select a,b from
select name1, address, wm_concat(coll) from (
select name1, address, collect(type) coll from table1 group by name1, address
) where not exists ((select * from tmp) minus (select * from table(coll)) )
这种条件的查询 不知道在么弄啊~~~~~~
分组查询会有随机性、、
-- Author :cosio(day day up)
-- Date :2011-10-12 10:25
-- Verstion:
----------------------------------------------------------------
--> 测试数据:[b]
with b
as
(
select 'name1' aa,'address' bb,1 cc from dual
union all
select 'name1','address', 2 from dual
union all
select 'name1','address', 3 from dual
union all
select 'name1','address', 4 from dual
)
select aa,bb from
(
select aa,bb,replace(max(substr(sys_connect_by_path(cc,' '),2)),' ','') dd
from b
start with cc=1
connect by cc=prior cc+1
group by aa,bb
)where dd='1234'
(SELECT DISTINCT A FROM TALBE WHERE 条件1 UNION ALL
SELECT DISTINCT A FROM TALBE WHERE 条件2 UNION ALL
SELECT DISTINCT A FROM TALBE WHERE 条件3 UNION ALL
......
SELECT DISTINCT A FROM TALBE WHERE 条件n )
GROUP BY A
HAVING COUNT(1) = n