我正在尝试构建这个查询,我认为在不使用子查询的情况下对其进行优化有点复杂。
| baggage_id | checkpoint | status |
|------------|------------|--------|
| 1 | 3 | 1 |
| 2 | 3 | 1 |
| 2 | 5 | 1 |
| 3 | 3 | 0 |
| 4 | 3 | 1 |
| 5 | 3 | 1 |
| 6 | 3 | 0 |
这应该是返回
| baggage_id | checkpoint | status |
|------------|------------|--------|
| 1 | 3 | 1 |
| 4 | 3 | 1 |
| 5 | 3 | 1 |
我只需要bagagges_id,其中:
有人能帮帮我吗?
谢谢。
发布于 2018-07-02 08:50:16
一种方法是在要排除的集合上使用折叠的左连接。
SELECT *
FROM bags b
LEFT JOIN (
-- Find set of baggage_id's to exclude
-- The group-by is to collapse the set to distinct baggage ID's
-- to avoid increasing multiplicity.
SELECT baggage_id, 1 as hasCheckpoint5AndStatus1
FROM bags
WHERE checkpoint = 5 AND status = 1
GROUP BY baggage_id
) b2
ON b.baggage_id = b2.baggage_id
WHERE b.checkpoint = 3 AND b.status = 1
-- If NULL, then baggage_id is NOT excluded
AND b2.hasCheckpoint5AndStatus1 IS NULL
https://stackoverflow.com/questions/51128007
复制相似问题