什么是下表的sql查询？

CREATE 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)?

问:显示table1的孩子,其颜色值为黄色和红色(两者都有)?

What is sql query?

什么是sql查询?

2 个解决方案

#1

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 whose color values are either yellow or red

IN子句将仅获取颜色值为黄色或红色的记录

The GROUP BY is necessary to remove duplicates

GROUP BY是删除重复项所必需的

COUNT(DISTINCT t.color) = 2 ensures valid kid values will be returned. Without the DISTINCT, two yellows/etc would satisfy the COUNT check

COUNT(DISTINCT t.color)= 2确保返回有效的kid值。如果没有DISTINCT,两个黄色/ etc将满足COUNT检查

#2

SELECT kid 
  FROM table1 as t, 
       table1 as t2 
 WHERE t1.color = 'yellow' 
   AND t1.kid = t2.kid 
   AND t2.color = 'red'

#1