Oracle 数据查询的问题~！

[koko0123]

表数据为：
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

[刹那]

select distinct A，B from 表名 where type = 1 or type = 2 or type = 3 or type = 4

[coolkisses]

楼主并没有说清楚，是必须 同时满足 4 个条件，还是，只需要满足一个条件即可？

只需满足一个条件，则，二楼的sql可用。
必须同时满足所有条件，就有些麻烦了。

建议思路， 滤重 -> 行转列 -> 过滤
例 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'

[fzqrf]

这样行不行：

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;

[fzqrf]

这样行不行：

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;

[eclipseluo]

没看懂啊

[coolkisses]

[Quote=引用 4 楼 fzqrf 的回复:]

这样行不行：

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……
[/Quote]

也是一种办法，但效率很低。 同一张表，需要查询 4 遍。
另外，不能写 select * from ，应该是 select a,b from

[jwd001]

with tmp as (select level from dual connect by level<5 )
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)) )

[xiaobn_cn]

select a,b from (select distinct a,b,type from table1) group by a,b having count(a) = 4 and sum(type) = 10;

[wylsy1]

8楼正解

select a,b from table1 group by a,b having count(distinct type) = 4;

[无爱大叔]

select a,b from 表名 where type between 1 and 4

select a,b from 表名 where type>=1 and type<=4

[koko0123]

[Quote=引用 2 楼 coolkisses 的回复:]

楼主并没有说清楚，是必须 同时满足 4 个条件，还是，只需要满足一个条件即可？

只需满足一个条件，则，二楼的sql可用。
必须同时满足所有条件，就有些麻烦了。

建议思路， 滤重 -> 行转列 -> 过滤
例 name1 address 1
name1 address 1
……
[/Quote]
是要同时满足4个条件~~~~~~~查询出来一条~~~~~？ 还没搞定呢

[koko0123]

[Quote=引用 4 楼 fzqrf 的回复:]

这样行不行：

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……
[/Quote]
这样太慢了~~，数据很多的 千万以上的数据

[koko0123]

在 oracle 中 执行这种类型的语句 ： select * from tale t where t.a=1 and t.a=2 and t=3
这种条件的查询 不知道在么弄啊~~~~~~

[cosio]

建议思路， 滤重 -> 行转列 -> 过滤

[koko0123]

就是查询其中的一列满足多个条件，查询出一条数据，该如何连接~~~~~用or 的话，全都出来，用and 不行，
分组查询会有随机性、、

[软发001]

没看明白。

[cosio]

----------------------------------------------------------------
-- 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'

[xiaobn_cn]

[Quote=引用 15 楼 koko0123 的回复:]
就是查询其中的一列满足多个条件，查询出一条数据，该如何连接~~~~~用or 的话，全都出来，用and 不行，
分组查询会有随机性、、
[/Quote]

SELECT AFROM
(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