有如下数据表
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)with
WITH
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 exists
SELECT *
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 join
SELECT 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增多时,参数不好处理。
欢迎讨论(各种数据库都可以)