>我需要获取表X
的 N 条记录,但我需要使用 INNER JOIN 过滤这些记录,并事先对连接的记录进行相应的排序。
我尝试做的事情的示例(可能不是 100% 准确,因为我使用的是 SQLAlchemy):
SELECT X.id
FROM X
INNER JOIN Y ON X.id = Y.other_id
WHERE Y.condition_one
ORDER BY Y.condition_two
LIMIT 10
FOR UPDATE OF X SKIP LOCKED;
当我这样做时,我得到的id
少于 10 个(可能是因为连接的行是有限的),但是,我无法执行DISTINCT
FOR UPDATE
.我想我不能使用子查询,因为FOR UPDATE
.
我该怎么办?
您可以使用横向连接:
SELECT X.id
FROM X
CROSS JOIN LATERAL (
SELECT Y.condition_two
FROM Y
WHERE X.id = Y.other_id
AND Y.condition_one
ORDER BY Y.condition_two
LIMIT 1) AS first_y
ORDER BY first_y.condition_two
LIMIT 10
FOR UPDATE OF X SKIP LOCKED;
或者(更简单)使用带有DISTINCT
的子选择:
SELECT X.id
FROM X
JOIN (SELECT DISTINCT X.id
FROM X
INNER JOIN Y ON X.id = Y.other_id
WHERE Y.condition_one
ORDER BY Y.condition_two
LIMIT 10) AS sub_x
ON X.id = sub_x.id
FOR UPDATE OF X SKIP LOCKED;
我在同时使用 DISTINCT 和 ORDER BY 时遇到了麻烦。
LATERAL 联接在理论上应该有效,但在进行实现时,我发现了另一种方法:
SELECT X.id
FROM X
JOIN (SELECT X.id, MIN(Y.condition_two) AS ordering
FROM X
INNER JOIN Y ON X.id = Y.other_id
WHERE Y.condition_one) AS sub_x
ON X.id = sub_x.id
ORDER BY ASC(sub_x.ordering)
LIMIT 10
FOR UPDATE OF X SKIP LOCKED;
如果我想使用 DESC 订购,我必须将 MIN 更改为 MAX。