考考你的SQL水平

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

select * from test where name in(select name from test where HOBBY='badminton') and HOBBY = 'basketball'
union all
select * from test where name in(select name from test where HOBBY='basketball') and HOBBY = 'badminton';

select* from test where name in(
select name from test where hobby in ('basketball', 'badminton')
  group by name having count(name)=2)

SELECT * FROM `TEST` T WHERE T.NAME IN
(SELECT T1.NAME FROM `TEST` T1 WHERE T1.HOBBY=`basketball`)
AND T.HOBBY='badminton'

select test.* from
(
select  distinct Name from test
where HOBBY = 'basketball' or  HOBBY = 'badminton'
group by Name
Having COUNT(HOBBY) =2
) as a inner join test
on a.NAME = test.NAME
where HOBBY = 'basketball' or  HOBBY = 'badminton'

SELECT *
  FROM (SELECT NAME, COUNT(T.HOBBY) AS HNUM
          FROM TEST T
         WHERE UPPER(T.HOBBY) = 'BADMINTON'
            OR UPPER(T.HOBBY) = 'BASKETBALL'
         GROUP BY T.NAME
         ORDER BY T.NAME) TEMP
 WHERE TEMP.HNUM > 1

 

 

 

SELECT *
  FROM TEST
 WHERE NAME IN (SELECT NAME
                  FROM TEST T
                 WHERE (UPPER(T.HOBBY) = 'BADMINTON' OR
                       UPPER(T.HOBBY) = 'BASKETBALL') HAVING
                 COUNT(T.HOBBY) > 1
                 GROUP BY T.NAME)

 ORDER BY NAME DESC
 

SELECT *
  FROM TEST TEST
 WHERE EXISTS (SELECT 1
          FROM TEST T
         WHERE (UPPER(T.HOBBY) = 'BADMINTON' OR
               UPPER(T.HOBBY) = 'BASKETBALL')
           AND T.NAME = TEST.NAME HAVING COUNT(T.HOBBY) > 1
         GROUP BY T.NAME)
 ORDER BY NAME ASC

 

既然已经知道basketball和badminton了，就只需要列出Name就可以了,然后在程序中对应做name - hobby。

select name from test where hobby in ('basketball','badminton') group by name having count(hobby)=2;

select a.NAME from tt a where a.HOBBY in('basketball','badminton')
group by a.NAME having count(a.NAME) = 2