MySQL查询WHERE条件

Question

我在 MySQL 数据库中有表 doTable。在 doTable 我有三个字段:

IdCode  int 11
State   char 2
id      int 11

单个 IdCode 字段 State 的值可以是:A、B、C、D 或TC.

+---------+-------+----+
| IdCode  | State | id |
+---------+-------+----+
| 6027713 | TC    |  1 |
| 7057816 | B     |  2 |
| 7047412 | TC    |  3 |
| 7047412 | A     |  4 |
| 6027713 | B     |  5 |
| 6027713 | B     |  6 |
| 6027713 | C     |  7 |
| 6040309 | TC    |  8 |
| 6040309 | A     |  9 |
| 6040309 | TC    | 10 |
| 6040309 | A     | 11 |
| 4041208 | TC    | 12 |
| 7060912 | TC    | 13 |
| 7060912 | D     | 14 |
+---------+-------+----+

我需要提取 doTable 的所有行，其中 State 值是 TC 只有当相同的 IdCode 包含 A、B、C 或 D。

我需要这个输出:

+---------+-------+----+
| IdCode  | State | id |
+---------+-------+----+
| 6027713 | TC    |  1 |
| 7057816 | B     |  2 |
| 7047412 | TC    |  3 |
| 7047412 | A     |  4 |
| 6027713 | B     |  5 |
| 6027713 | B     |  6 |
| 6027713 | C     |  7 |
| 6040309 | TC    |  8 |
| 6040309 | A     |  9 |
| 6040309 | TC    | 10 |
| 6040309 | A     | 11 |
| 7060912 | TC    | 13 |
| 7060912 | D     | 14 |
+---------+-------+----+

并在输出中排除 ID 号为 12 的行，因为我没有相同 IdCode 的值 A、B、C 或 D

+---------+-------+----+
| IdCode  | State | id |
+---------+-------+----+
| 4041208 | TC    | 12 |
+---------+-------+----+

我已经尝试过这个查询但是空集。

mysql> SELECT
    *
FROM
    `dotable`
WHERE
    (
        State = 'A'
        OR State = 'B'
        OR State = 'C'
        OR State = 'D'
    )
AND State = 'TC';
Empty set

mysql> 

你能帮帮我吗？

-- ----------------------------
-- Table structure for `dotable`
-- ----------------------------
DROP TABLE IF EXISTS `dotable`;
CREATE TABLE `dotable` (
  `IdCode` int(11) DEFAULT NULL,
  `State` char(2) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=15 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of dotable
-- ----------------------------
INSERT INTO `dotable` VALUES ('6027713', 'TC', '1');
INSERT INTO `dotable` VALUES ('7057816', 'B', '2');
INSERT INTO `dotable` VALUES ('7047412', 'TC', '3');
INSERT INTO `dotable` VALUES ('7047412', 'A', '4');
INSERT INTO `dotable` VALUES ('6027713', 'B', '5');
INSERT INTO `dotable` VALUES ('6027713', 'B', '6');
INSERT INTO `dotable` VALUES ('6027713', 'C', '7');
INSERT INTO `dotable` VALUES ('6040309', 'TC', '8');
INSERT INTO `dotable` VALUES ('6040309', 'A', '9');
INSERT INTO `dotable` VALUES ('6040309', 'TC', '10');
INSERT INTO `dotable` VALUES ('6040309', 'A', '11');
INSERT INTO `dotable` VALUES ('4041208', 'TC', '12');
INSERT INTO `dotable` VALUES ('7060912', 'TC', '13');
INSERT INTO `dotable` VALUES ('7060912', 'D', '14');

Answer 1

试试这个:

SELECT *
FROM `dotable`
WHERE IdCode IN 
    (SELECT IdCode 
     FROM `dotable`
     WHERE State IN ('A','B','C','D'))

解释:

内部查询从值A、B、C和D中选择至少一个状态的IdCodes。外部查询从内部查询的结果中选择具有IdCodes的所有记录。

结果:

IDCODE  STATE   ID
6027713 TC      1
7057816 B       2
7047412 TC      3
7047412 A       4
6027713 B       5
6027713 B       6
6027713 C       7
6040309 TC      8
6040309 A       9
6040309 TC      10
6040309 A       11
7060912 TC      13
7060912 D       14

在 SQL Fiddle 中查看结果.