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语句查出哪些人即会玩basketball又会玩badminton,找出这样的name-hobby组合。(这样表达如何?)
即第5, 6 ,7 ,8行
select *
from testsql
WHERE HOBBY='basketball' and NAME in
(
select NAME
from testsql
where HOBBY ='badminton'
)