CRE开发者_StackOverflowATE TABLE table1(kid char(2),color varchar(9));
INSERT INTO table1('k1'.'yello');
INSERT INTO table1('k1'.'red');
INSERT INTO table1('k2'.'yello');
INSERT INTO table1('k2'.'blue');
INSERT INTO table1('k3'.'yello');
Q: Display kid
of table1 which has color
values yellow and red (both of them)?
What is sql query?
Use:
SELECT t.kid
FROM TABLE1 t
WHERE t.color IN ('yellow', 'red')
GROUP BY t.kid
HAVING COUNT(DISTINCT t.color) = 2
- The
IN
clause will get only records whosecolor
values are either yellow or red - The
GROUP BY
is necessary to remove duplicates COUNT(DISTINCT t.color) = 2
ensures validkid
values will be returned. Without theDISTINCT
, two yellows/etc would satisfy theCOUNT
check
SELECT kid
FROM table1 as t,
table1 as t2
WHERE t1.color = 'yellow'
AND t1.kid = t2.kid
AND t2.color = 'red'
精彩评论