考考你的SQL水平

in oracle
------------------------------
SELECT NAME from test
WHERE hobby IN ('basketball','badminton')
GROUP BY NAME HAVING COUNT(*) >=2

select * from test a
WHERE hobby = 'basketball'
AND EXISTS (SELECT * from TEST_test b
WHERE a.name = b.name AND b.hobby = 'badminton');

这两种速度感觉差不多

 select a.name,a.hobby from test a,
 (
 select NAME,avg(
        case
          when HOBBY = 'badminton' then
           1
          when HOBBY = 'basketball' then
           2
        end) s
   from test group by name
  ) b where round(s)<>s and a.hobby in ('badminton','basketball') and a.name=b.name

 

如果只用in 和having还是不妥当。如果存在几条''badminton'，却不存在'basketball',会扰乱查询结果。

 

通过用1和2，求平均数，如果平均数四舍五入不等于他本身，就说明，两者都存在。

select t.*
  from test t
where exists (select 1
          from (select name from test where hobby = 'basketball'
                INTERSECT
                select name from test where hobby = 'badminton')t4
         where t4.name = t.name);

又看了一遍LZ的说明，看了半天 说明查出的结果才大概明白。

是要查出 hobby为basketball或badminton 的记录， 这些记录的name 要在整张表中既要对应 有hobby为basketball，又要对应有hobby为badminton的记录：


select * from test
where name in (select name from
                  (select distinct name, hobby  from test where hobby in ('basketball', 'badminton'))
               group by name having count(*) > 1)
  and hobby in ('basketball', 'badminton');

数据量不大时，怎么查都没关系
数据量大是，实际哪种查法最有要根据数据、索引情况来定




楼上很多没有考虑 如果记录中存在以下这种情况的处理：
insert into test values('Bill','basketball'); 
insert into test values('Bill','basketball'); 
insert into test values('Bill','basketball'); 

即：name为Bill，对应多行basketball，而一行badminton都没有的情况

select aa.name1,aa.hobby1 from ( select a.name as name1,a.hobby as hobby1,b.name as name2,b.hobby as hobby2 from (select * from test where hobby ='basketball') a,(select * from test where hobby ='badminton') b where a.name = b.name) aa  where aa.hobby1 = 'basketball' union
select aa.name2,aa.hobby2 from ( select a.name as name1,a.hobby as hobby1,b.name as name2,b.hobby as hobby2 from (select * from test where hobby ='basketball') a,(select * from test where hobby ='badminton') b where a.name = b.name) aa  where aa.hobby2 = 'badminton';

做出了针对这种情况的修改。
性能未必好，但是语意应该比较清楚，关键点是过滤符合条件的“name”记录。

select * from test  
where name in  
(  
    select name from  
    (  
        --filter name of collections
        select name,sum(hobby_basket),sum(hobby_badmin) from 
        (
            select name,hobby,
            decode(hobby,'basketball',1,0) hobby_basket,
            decode(hobby,'badminton',1,0) hobby_badmin 
            from test
        )
        group by name
        having sum(hobby_basket) > 0 and sum(hobby_badmin) > 0
    )  
)  
and hobby in ('basketball','badminton') 

-----

表示看不懂题目

看了评论才看懂LZ所要表达的意思  先用  wm_concat 合并 然后直接精确查找

Oracle
select distinct(t1.name) from test t1 ,test t2 where t2.hobby = 'basketball' and t1.hobby='badminton'