mysql - 如何跨组过滤表格及其内容

Question

在我的表中是必须生产的项目订单(在我的例子中是简单化的)。我必须只过滤那些完全完成的订单。如果所有订单(具有相同的 order_id)的状态为“4”或“-1”，则订单已完成。

"id"    "order_id"  "item_nr"   "status"
"689"   "4"         "M2223"     "3"
"690"   "4"         "K3224"     "-1"
"691"   "4"         "X1837"     "4"
"692"   "4"         "M2338"     "0"
"693"   "5"         "E7273"     "4"
"694"   "5"         "E8883"     "-1"
"695"   "5"         "B9217"     "4"
"696"   "5"         "B2662"     "4"

在示例中:我想得到结果:

"693"   "5"         "E7273"     "4"
"694"   "5"         "E8883"     "-1"
"695"   "5"         "B9217"     "4"
"696"   "5"         "B2662"     "4"

如何选择这个？

Answer 1

一个选择是将您的表LEFT JOIN 到一个子查询，该子查询标识所有不 匹配的订单，然后只保留不匹配的订单的记录。这意味着那些匹配的订单没有任何状态不完整的记录。

SELECT t1.*
FROM yourTable t1
LEFT JOIN
(
    SELECT order_id
    FROM yourTable
    WHERE status NOT IN ('4', '-1')
) t2
    ON t1.order_id = t2.order_id
WHERE t2.order_id IS NULL

输出:

此处演示:

Rextester

根据@shadow 的评论，我们还可以使用NOT EXISTS 来表达这个查询:

SELECT t1.*
FROM yourTable t1
WHERE NOT EXISTS
(
    SELECT 1
    FROM yourTable t2
    WHERE t2.status NOT IN ('4', '-1') AND t2.order_id = t1.order_id
)