考考你的SQL水平

 有如下数据表

create table test (NAME varchar(20) not null, HOBBY varchar(20) not null);  
 insert into test values('Adam','basketball');
 insert into test values('Bill','basketball');
 insert into test values('Bill','football');
 insert into test values('Cyper','basketball');
 insert into test values('Cyper','badminton');
 insert into test values('David','basketball');
 insert into test values('David','badminton');
 insert into test values('David','table tennis');

 

使用SQL语句查出hobby即包含basketball,又包含badminton的name所在的行：

使用SQL语句查出哪些人即会玩basketball又会玩badminton，找出这样的name-hobby组合。（这样表达如何？）

即第5， 6 ，7 ，8行

 

 

我想了几种SQL语句。不知道还没有有别的。

--(1)with
WITH
  NAME_MATCHES AS
     (SELECT NAME
        FROM TEST
        WHERE HOBBY IN ('basketball', 'badminton')
        GROUP BY NAME
        HAVING COUNT(NAME) = 2)
SELECT T.*
  FROM TEST AS T, NAME_MATCHES AS NM
  WHERE T.NAME = NM.NAME;

--(2)exists and exists
SELECT *
  FROM TEST AS T1
  WHERE EXISTS (SELECT *
          FROM TEST AS T2
          WHERE T2.NAME = T1.NAME AND HOBBY = 'basketball')
    AND EXISTS (SELECT *
          FROM TEST AS T3
          WHERE T3.NAME = T1.NAME AND HOBBY = 'badminton');

--(3)self join
SELECT T1.*
  FROM TEST AS T1, TEST AS T2, TEST AS T3
  WHERE T1.NAME = T2.NAME AND T1.NAME = T3.NAME AND T2.HOBBY = 'basketball'
    AND T3.HOBBY = 'badminton';

 

第1种是我目前采纳的，稍微有点长

第3种最简单，可是扩展性不好，当hobby增多时，参数不好处理。

 

欢迎讨论（各种数据库都可以）

 

感言：没想到帖子的回复量突然井喷，先谢谢各位，等我研究一番后，附上所有正确的SQL，并给出性能上的分析。

 

 

 

这个题目难吗？怎么没人回

In Oracle:

select *
from test
where test.name in
(
select a.name from
(select * from test where hobby = 'basketball') a,
(select * from test where hobby = 'badminton') b
where a.name = b.name
)

楼主的查询语句把第9行查出来了，要加上*.hobby in('basketball', 'badminton')
上面要求的是5，6,7,8没有第9行
select * from test where name in(select a.name from test  a,test b where a.name=b.name and a.hobby='basketball' and b.hobby='badminton') and hobby in('basketball','badminton')

select  * from (
select username ,
sum(bus ) bus,
sum(bad ) bad,
sum(....)
from
(
select username
  ,decode(hobby,'basketball',1,0,0  ) bus
  ,decode(hobby,'badminton',1,0,0) bad
  ......
)
t group by username) a
where 1=1 
and bus>0
and bad >0


这个方案需要ibatis支持
点部分可以用 <iterater>标签包括
不好的地方由于他们不能使用# 只能用$ 所以有注入可能性.

In Oracle:
Exists:
SELECT T1.*   FROM  Test   T1  WHERE EXISTS (SELECT T2.*   FROM Test   T2  WHERE T2.NAME = T1.NAME AND T2.hobby = 'basketball')   AND EXISTS (SELECT T3.*   FROM Test  T3    WHERE T3.NAME = T1.NAME AND T3.hobby = 'badminton') and T1.hobby in ('basketball','badminton');
Self join:
select * from Test where name in(select a.name from Test  a,Test b where a.name=b.name and a.hobby='basketball' and b.hobby='badminton') and hobby in('basketball','badminton')

select a.* from test a inner join test b on a.name = b.name
where a.hobby in('basketball','badminton') and b.hobby in('basketball','badminton') and a.hobby!=b.hobby

SELECT DISTINCT NAME FROM test WHERE HOBBY IN ('basketball','badminton')

select t.* from test t,
(select 'basketball' name from dual
union all
select 'badminton' from dual) x
where t.hobby=x.name

IN ORACLE

--hobby即包含basketball,又包含badminton的name
select * from test t
where t.HOBBY = 'basketball'
and exists (select 1 from test t1 where t.name=t1.name and t1.HOBBY  = 'badminton')；

--需要扩展性的写法
select name from (
   select t.name as name,t.hobby as hoby
     from test t group b t.name,t.hobby
   ) res where res.hobby in ('basketball','badminton') GROUP BY name
  HAVING COUNT(res.name) >= 2


附带：
LZ第（1）条是“只包含basketball和badminton”，而非“即包含basketball,又包含badminton”;
LZ第（2）条是个人无想法；
LZ第（3）条，个人无想法；