sql - 带有 View 的 PostgreSQL ORDER BY

Question

假设我想编写一个使用 VIEW 的简单 SELECT 查询:

CREATE TEMP VIEW people AS
SELECT 
     p.person_id
    ,p.full_name
    ,p.phone
FROM person p
ORDER BY p.last_name;

SELECT
     p.*
    ,h.address
    ,h.appraisal
FROM people p
LEFT JOIN homes h
     ON h.person_id = p.person_id
ORDER BY p.last_name, h.appraisal;

这里明显的问题是，当我去执行最后的 ORDER BY 时，p.last_name 不再可用。

我如何对最终查询进行排序，以便 people View 的原始序列遵循最终查询？

这里的简单解决方案是只在 View 中包含 p.last_name。我不想那样做 - 我的真实世界示例(复杂得多)使这成为一个问题。

我过去对临时表做过类似的事情。例如，我使用 CREATE TEMP TABLE testing WITH OIDS 创建表，然后执行 ORDER BY testing.oid 以传递原始序列。

是否可以对 View 执行相同的操作？

Answer 1

如果您使用 row_number() over()，这是可能的。

这是一个例子:

SELECT
    p.*
    ,h.address
    ,h.appraisal
FROM (SELECT *, row_number() over() rn FROM people) p
LEFT JOIN homes h
    ON h.person_id = p.person_id
ORDER BY p.rn, h.appraisal;

这里是 SQL Fiddle你可以测试。

正如@Erwin Brandstetter 正确指出的那样，使用 rank() 将产生正确的结果并允许对其他字段(在本例中为评估)进行排序。

SELECT
    p.*
    ,h.address
    ,h.appraisal
FROM (SELECT *, rank() over() rn FROM people) p
LEFT JOIN homes h
    ON h.person_id = p.person_id
ORDER BY p.rn, h.appraisal;

这样想，使用 row_number()，它将始终仅按该字段排序，而不管任何其他排序参数。通过在关系相同的地方使用 rank()，可以轻松搜索其他字段。

祝你好运。