SQL 选择匹配列表中所有项目的一组行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15977126/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Select group of rows that match all items in a list
提问by user1229351
Assume I have two tables:
假设我有两个表：
cars– list of cars
cars– 汽车清单
carname | modelnumber | ...
passedtest– contains every test that a car passed:
passedtest– 包含汽车通过的每项测试：
id | carname | testtype | date | ...
1 | carA | A | 2000 |
2 | carB | C | 2000 |
3 | carC | D | 2001 |
4 | carA | C | 2002 |
Now, how can I select a car from the passedtesttable that passed all tests (A, B, C, D)?
现在，我如何从passedtest通过所有测试（A、B、C、D）的表中选择一辆汽车？
I tried the INstatement but it also matches cars that pass even one test. I am looking for a statement to match allvalues in a list across all rows.
我尝试了该IN声明，但它也匹配通过一项测试的汽车。我正在寻找一个语句来匹配所有行列表中的所有值。
回答by MarcinJuraszek
How about this?
这个怎么样？
SELECT carname
FROM PassedTest
GROUP BY carname
HAVING COUNT(DISTINCT testtype) = 4
You can also use it as an inner statement for taking info from the carstable:
您还可以将其用作从cars表中获取信息的内部语句：
SELECT *
FROM cars
WHERE carname IN (
SELECT carname
FROM PassedTest
GROUP BY carname
HAVING COUNT(DISTINCT testtype) = 4
)
回答by John Woo
This type of problem is called Relational Division.
这种类型的问题称为Relational Division.
SELECT a.*
FROM Cars a
INNER JOIN
(
SELECT CarName
FROM PassedTest
WHERE testType IN ('A', 'B', 'C', 'D')
GROUP BY CarName
HAVING COUNT(*) = 4
) b ON a.CarName = b.CarName
if a UNIQUEconstraint was not enforce on TestTypefor every CarNameon table PassedTesta DISTINCTkeyword is required on COUNT()so it will only count unique values.
如果UNIQUE没有对TestType每个CarName表强制执行约束，则需要PassedTest一个DISTINCT关键字，COUNT()因此它只会计算唯一值。
SELECT a.*
FROM Cars a
INNER JOIN
(
SELECT CarName
FROM PassedTest
WHERE testType IN ('A', 'B', 'C', 'D')
GROUP BY CarName
HAVING COUNT(DISTINCT TestType) = 4
) b ON a.CarName = b.CarName
but if you are only interested on the CARNAMEthen you don't need to join the tables. Querying on table PassedTestwill suit your needs.
但如果你只对 感兴趣，CARNAME那么你不需要加入表格。在表上查询PassedTest将满足您的需求。
SELECT CarName
FROM PassedTest
WHERE testType IN ('A', 'B', 'C', 'D')
GROUP BY CarName
HAVING COUNT(*) = 4
回答by Salman A
You want to perform relational division, an operation that is not implemented in SQL. Here is an example where we have a product-suppliertable and a required-productstable:
您想要执行关系除法，这是 SQL 中未实现的操作。这是一个示例，其中我们有一个product-supplier表和一个required-products表：
CREATE TABLE product_supplier (
product_id int NOT NULL,
supplier_id int NOT NULL,
UNIQUE (product_id, supplier_id)
);
INSERT INTO product_supplier (product_id, supplier_id) VALUES
(1, 1),
(2, 1),
(3, 1),
(1, 2),
(2, 2),
(3, 2),
(4, 2),
(2, 3),
(3, 3),
(4, 3);
CREATE TABLE reqd (
product_id int NOT NULL,
UNIQUE (product_id)
);
INSERT INTO reqd (product_id) VALUES
(1),
(2),
(3);
... and we want to find all suppliers that supply ALL required products and perhaps others. The result in the above example would be supplier 1 and 2.
...我们希望找到所有供应商提供所有必需的产品，也许还有其他产品。上例中的结果是供应商 1 和 2。
The most straight forward solution is this:
最直接的解决方案是这样的：
SELECT product_supplier.supplier_id
FROM product_supplier
LEFT JOIN reqd ON product_supplier.product_id = reqd.product_id
GROUP BY product_supplier.supplier_id
HAVING COUNT(reqd.product_id) = (SELECT COUNT(*) FROM reqd);
+-------------+
| supplier_id |
+-------------+
| 1 |
| 2 |
+-------------+
And if we want to find all suppliers that supply ALL required products and no others (exact division/no remainder) then add one more condition to the above:
如果我们想找到提供所有必需产品而没有其他产品的所有供应商（精确划分/无余数），则在上述条件中再添加一个条件：
SELECT product_supplier.supplier_id
FROM product_supplier
LEFT JOIN reqd ON product_supplier.product_id = reqd.product_id
GROUP BY product_supplier.supplier_id
HAVING COUNT(reqd.product_id) = (SELECT COUNT(*) FROM reqd)
AND COUNT(product_supplier.product_id) = (SELECT COUNT(*) FROM reqd);
+-------------+
| supplier_id |
+-------------+
| 1 |
+-------------+
An alternate solution is to rephrase the problem: select suppliers where a required product does not exist that does not exist in the products supplied by the supplier. Hmmm:
另一种解决方案是重新表述这个问题：选择供应商提供的产品中不存在所需产品的供应商。嗯：
SELECT DISTINCT supplier_id
FROM product_supplier AS ps1
WHERE NOT EXISTS (
SELECT *
FROM reqd
WHERE NOT EXISTS (
SELECT *
FROM product_supplier AS ps2
WHERE ps1.supplier_id = ps2.supplier_id AND ps2.product_id = reqd.product_id
)
);
+-------------+
| supplier_id |
+-------------+
| 1 |
| 2 |
+-------------+
