如果提供的所有或一个类别都存在，则不要检索
如果提供的所有或一个类别都存在，则不要检索
提问于 2016-02-16 08:14:26
我有下面的数据库，我的问题是我想查询my_list表中的所有my_list，其中对应的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被检索，因为它有两个info_risk_code，它们都是400：
my_list.list_id info_risk_code list_risk_code
3 '600' '400'现在，我的代码在下面，得到1和3，因为它们都包含非600 info_risk_code。这是不正确的：
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');现在用这个查询编码将近一个小时，需要一些想法。谢谢
回答 2
Stack Overflow用户
回答已采纳
发布于 2016-02-16 08:20:47
您可以使用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
Stack Overflow用户
发布于 2016-02-16 09:02:36
您可以使用group by查询只选择list_id值，其中所有info_risk_codes为400，而关联的list_risk_code为600。
select ml.list_id
from my_list ml
join my_info_list mil on ml.list_id = mil.list_id
join my_info mi on mil.info_id = mi.info_id
where ml.list_risk_code = '600'
group by ml.list_id
having sum(info_risk_code <> '400') = 0http://sqlfiddle.com/#!9/4b9e9/1
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/35427114
复制相关文章
相似问题
腾讯云开发者
