MySQL 查询根据第二个表上的状态丢弃记录

Question

我有两个表:

CREATE TABLE qr_code (
    id   int(10) primary key auto_increment,
    code int(10) not null
);

CREATE TABLE area_qrcode (
    id         int(10) primary key auto_increment,
    area_id    int(10) not null,
    qr_code_id int(10) not null,
    status     set('open', 'close')
);

在我的表中，我有以下记录:

-- qr_code
+----+------+
| id | code |
+----+------+
|  1 |  200 |
|  2 |  201 |
|  3 |  202 |
+----+------+

-- areaqr_code
+----+---------+------------+--------+
| id | area_id | qr_code_id | status |
+----+---------+------------+--------+
|  1 |       2 |          1 | open   |
|  2 |       4 |          3 | close  |
+----+---------+------------+--------+

我想从 qr_code 表中选择 area_qrcode 表中所有未处于 open 状态的记录。 qr_code.id对应area_qrcode.qr_code_id。

一个好的结果集应该是:

+----+------+
| id | code |
+----+------+
|  2 |  201 |
|  3 |  202 |
+----+------+

我正在使用以下查询，但它没有像我预期的那样工作，因为如果表 area_qrcode 没有与 qr_code 中的值对应的值，那么我得到这些记录没有结果:

SELECT
    qr.*
FROM
    `qr_code` as qr,
    `area_qrcode` as aq 
WHERE
    (
        aq.qr_code_id = qr.id and
        aq.status != 'open'
    )
    OR
    (
        aq.qr_code_id != qr.id
    );

即使 areaqr_code 中没有相关记录，我可以更改什么以从 qr_code 获取记录？

Answer 1

您可以通过子选择获得结果:

SELECT 
    * 
FROM 
    `qr_code` as qr
where 
    qr.id NOT IN (
        SELECT qr_code_id 
        FROM area_qrcode 
        WHERE status = 'open'
    );

Demo

使用 LEFT JOIN 也可以获得结果:

SELECT 
    qr.* 
FROM 
    qr_code as qr 
LEFT JOIN 
    area_qrcode as aq 
ON
    qr.id = aq.qr_code_id
AND
    aq.status = 'open'
WHERE
    aq.qr_code_id IS NULL;

Updated Demo

第三种可能性(不像第一种那么直观):

SELECT 
    *
FROM 
    `qr_code` as qr
WHERE NOT EXISTS (
    SELECT
        1
    FROM
        area_qrcode
    WHERE
        status = 'open'
    AND
        qr_code_id = qr.id
);  

Demo