SQL题目

uniquejava

 有如下数据表
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所在的行：
即第5， 6 ，7 ，8行
 
 
我想了几种SQL语句。不知道还没有有别的。
--(1)withWITH  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 existsSELECT *  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 joinSELECT 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增多时，参数不好处理。
 
欢迎讨论（各种数据库都可以）