一个sql问题研究

            假设有下面的数据表： 

            

create table test (
ID` bigint(20) NOT NULL AUTO_INCREMENT,
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语句查出哪些人即会玩basketball又会玩badminton，找出这样的name组合，即输出：

   Cyper,David. 我想到了用下面的一些方法：

•  使用最简单的方式,这个里面由于使用了group by，个人感觉不需要加distinct

select name FROM TEST 
WHERE hobby in ('basketball','badminton') 
group by name having count(name) = 2;

 

 

• 使用比较简单的in子查询来实现，需要加distinct

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

 

 

 

•  使用子查询，个人感觉此处不需要加distinct

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

 

 

•  使用复杂的子查询，但是效率不敢恭维

select distinct(name) from Test as t1
where exists (
    select 1 from Test as t2
    where t2.name = t1.name and hobby = 'basketball')
and exists (
    select 1 from Test as t3
     where t3.name = t1.name and hobby = 'badminton'
);

 

 

•  使用表联合查询

select distinct(t1.name)
from Test t1,Test t2,Test t3
where t1.name = t2.name and t1.name = t3.name and 
t2.hobby = 'basketball' and t3.hobby = 'badminton';

 

 

•  根据题目的要求进行枚举分析

select t1.* from Test t1, Test t2 where t1.name=t2.name 
and( 
t1.hobby='basketball' and t2.hobby='badminton' or 
t2.hobby='basketball' and t1.hobby='badminton' 
)

 

 

 

 

 

评论

6 楼 肥啦A梦 2013-12-02  

肥啦A梦 写道

结合楼上，出现重复数据，我认为如此比较合适的一句~
SELECT NAME FROM TEMP_TEST WHERE HOBBY = 'basketball' OR HOBBY = 'badminton' GROUP BY NAME HAVING COUNT(*) >= 2;

这种前提是没有考虑数据量的操作，如果数据量太大，效率提示还是要通过临时表~

5 楼 肥啦A梦 2013-12-02  

结合楼上，出现重复数据，我认为如此比较合适的一句~
SELECT NAME FROM TEMP_TEST WHERE HOBBY = 'basketball' OR HOBBY = 'badminton' GROUP BY NAME HAVING COUNT(*) >= 2;

4 楼 asialee 2013-12-01  

小网客 写道

insert into test values('Bill','basketball'); 
insert into test values('Bill','basketball');
如果里面有这样的数据 你怎么办？我的观点是 只能通过子查询

说的有道理，这个地方，如果我们分两种情况就有意思了。

3 楼 小网客 2013-12-01  

insert into test values('Bill','basketball'); 
insert into test values('Bill','basketball');
如果里面有这样的数据 你怎么办？我的观点是 只能通过子查询

2 楼 曾经de迷茫 2013-12-01  

select name from test a where HOBBY = 'basketball' and exists 
(select * from test b where a.name=b.name and b.hobby = 'badminton');

http://ideone.com/geiOPb

1 楼 asialee 2013-11-30  

大家可以集思广益