考考你的SQL水平

select a.name,a.hobby from test a,(
       select count(*) as cou,name from test t where t.hobby in ('basketball','badminton') group by name having count(*)=2
) b
where a.name = b.name and a.hobby in ('basketball','badminton')

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

我的postgreSQL

不考虑效率的话,下面的语句可以, 扩展起来比较容易, 也比较好理解
select t1.name , t1.hobby
from test t1
join test t2 on t1.name = t2.name
where t1.hobby = 'basketball' and t2.hobby = 'badminton'
union
select t2.name , t2.hobby
from test t1
join test t2 on t1.name = t2.name
where t1.hobby = 'basketball' and t2.hobby = 'badminton'

select name from test where
hobby ='basketball'
and name in(select name from test where hobby='badminton')
不过这种情况感觉只能是满足少量，太多的话可能不太好了
而且，我感觉这个是要出来的只是两个名字，而不是像LZ说的那样的 5 6 7 8 行，
那样的话，不就是重复了吗？
既然是需要使用SQL语句查出哪些人即会玩basketball又会玩badminton的，那么只需要有这两个都会玩的人名就OK了。
不是么？

SELECT NAME FROM (
SELECT DISTINCT NAME,HOBBY FROM test A
WHERE A.HOBBY='basketball' OR A.HOBBY='badminton'
) TMP
GROUP BY TMP.NAME
HAVING COUNT(NAME)>1



看了下，好像我的执行效率最高：）

貌似不能执行吧..

我个人觉得题目问的不好
使用SQL语句查出哪些人即会玩basketball又会玩badminton，找出这样的name-hobby组合
这里我已经知道了hobby是什么了（basketball，badminton），我还要查它做什么
只用知道name就ok了

这样可以不？

select distinct name from test t1 where t1.hobby='basketball'
and exists(
    select null from test t2 where t2.name=t1.name and t2.hobby='badminton'
);

mysql

  select a.NAME, HOBBY
  from
  (
  select NAME  from test where
  HOBBY IN ('basketball', 'badminton')
  group by NAME having count(distinct HOBBY)=2)
  a
  join test using(NAME)
  where  HOBBY IN ('basketball', 'badminton');

标准答案：


select  name,hobby
  from  (
         select  a.name,a.hobby
           from  Test a
          where  hobby='basketball'
            and  exists(
                 select 1 from Test b
                  where hobby='badminton'
                    and b.name=a.name
                 )
        ) a
where  hobby in ('basketball','badminton')


任何解决方案都需要解决两个问题：
1. 找到有这两个hobby的name
2. 只输出hobby是这两个之一的记录。

SQL不同，只是表现形式而已。