mysql - 不检索所有或其中一个提供的类别是否存在

Question

我有以下数据库，我的问题是我想查询 my_list 表中的所有 list_id，其中对应的 list_risk_code 是 '600' 及其info_risk_code 都是'400'

table: my_info_list
info_id        list_id
1              1
2              1
3              1
4              2
5              2
6              3
7              3

table: my_info 
info_id        info_risk_code
1              '400'
2              '600'
3              '400'
4              '600'
5              '600'
6              '400'
7              '400'


table: my_list
list_id        list_risk_code
1              '600'
2              '600'
3              '600'

下面是我想要的输出，因为 list_id 1 有 3 个 info_risk_code(400,600 和 400)，但其中一个是 600，因此不会包含在内。 list_id 2 有 2 个 info_risk_code(600,600) 但它们都是 600，所以它也被忽略了。只有 list_id 3 被检索，因为它有 2 个 info_risk_code 都是 400:

my_list.list_id        info_risk_code     list_risk_code
3                      '600'              '400'

现在我的代码在下面，得到 1 和 3，因为它们都包含非 600 信息风险代码。这是不正确的:

SELECT DISTINCT
    ml.list_id
   ,info_risk_code as c_rr
   ,list_risk_code as a_rr
FROM 
    my_list AS ml
    INNER JOIN my_info_list AS mil ON mil.list_id = ml.list_id
    INNER JOIN my_info AS mi ON mil.info_id = mi.info_id
WHERE 
    (
            (info_risk_code = '600' OR info_risk_code = '360') 
        AND (NOT list_risk_code = '600' AND NOT list_risk_code = '360')
    )
    OR 
    (
            (NOT info_risk_code = '600' AND NOT info_risk_code = '360') 
        AND (list_risk_code = '600' OR list_risk_code = '360')
    )

为了您的试用/引用，您可以使用这个:

CREATE TABLE my_info_list(
    info_id INT,
    list_id INT
);

CREATE TABLE my_info(
    info_id INT,
    info_risk_code varchar(5)
);

CREATE TABLE my_list(
    list_id INT,
    list_risk_code varchar(5)
);


INSERT INTO my_info_list VALUES (1,1);
INSERT INTO my_info_list VALUES (2,1);
INSERT INTO my_info_list VALUES (3,1);
INSERT INTO my_info_list VALUES (4,2);
INSERT INTO my_info_list VALUES (5,2);
INSERT INTO my_info_list VALUES (6,3);
INSERT INTO my_info_list VALUES (7,3);

INSERT INTO my_info VALUES (1,'400');
INSERT INTO my_info VALUES (2,'600');
INSERT INTO my_info VALUES (3,'400');
INSERT INTO my_info VALUES (4,'600');
INSERT INTO my_info VALUES (5,'600');
INSERT INTO my_info VALUES (6,'400');
INSERT INTO my_info VALUES (7,'400');

INSERT INTO my_list VALUES (1,'600');
INSERT INTO my_list VALUES (2,'600');
INSERT INTO my_list VALUES (3,'600');

现在用这个查询编码将近一个小时，需要一些想法。谢谢

Answer 1

您可以使用NOT EXISTS:

SELECT DISTINCT ml.list_id, info_risk_code as c_rr, list_risk_code as a_rr
FROM my_list AS ml
INNER JOIN my_info_list AS mil
  ON mil.list_id = ml.list_id
INNER JOIN my_info AS mi
  ON mil.info_id = mi.info_id
WHERE mi.info_risk_code = '400' AND 
      NOT EXISTS (SELECT 1
                  FROM my_info_list AS mil2
                  INNER JOIN my_info AS mi2 ON mil2.info_id = mi2.info_id
                  WHERE mil2.list_id = ml.list_id AND mi2.info_risk_code <> '400')

Demo here