PostgreSQL查询包括WITH子查询

Question

从下表（名为 status）中，我需要提取在2015年底状态为'01'的城镇代码。列status_date存储了城镇更改其状态的日期。

gid  | town      | status  | status_date
-----+-----------+---------+-------------
1    | 86001     | 00      | 2000-01-01
2    | 86001     | 01      | 2016-03-01
3    | 86002     | 01      | 2000-01-01
4    | 86003     | 00      | 2000-01-01
5    | 86003     | 01      | 2015-03-01
6    | 86003     | 02      | 2015-09-01

我可以通过以下较长的查询语句来实现这一点：

WITH tab AS (SELECT town, MAX(status_date) FROM status GROUP BY town)

  SELECT 
    t.town

  FROM tab t
    LEFT JOIN status s ON t.town = s.town AND t.max = s.status_date 

  WHERE t.max < '2016-01-01' AND s.status = '01' ; 

结果如下：

town   
-------
86002

有没有简化这个查询的想法？ WITH 是必要的吗？

---

为了进行测试创建表格：

CREATE TABLE status (gid serial NOT NULL, town CHARACTER VARYING(5), status CHARACTER VARYING(2), status_date DATE) ;

INSERT INTO status (town, status, status_date) VALUES
  ('86001', '00', '2000-01-01'),
  ('86001', '01', '2016-03-01'),
  ('86002', '01', '2000-01-01'),
  ('86003', '00', '2000-01-01'),
  ('86003', '01', '2015-03-01'),
  ('86003', '02', '2015-09-01') ;

- wiltomap

---

您的查询不正确，如果一个城镇在2016年从“01”更改为其他值。 - Gordon Linoff

2016年以后的城镇无论如何都不包括在查询中。如果一个城镇在2016年改变了其状态，会有什么问题？ - wiltomap

Answer 1

你可以使用 distinct on 来实现这一点：

select s.*
from (select distinct on (s.town) s.*
      from status s
      where s.status_date < '2016-01-01'
      order by s.town, s.status_date desc
     ) s
where status = '01';

这个查询将获取每个城镇截至2015年底的最新状态。然后外部查询选择那些是01。

- Gordon Linoff

---

我刚刚编辑了您的帖子，添加了 ASC 和 DESC 排序指示，这似乎是必要的，以便 DISTINCT ON 子句可以选择每个城镇行集中适当的第一行。感谢 @Gordon Linoff，这比我的提议更简洁。 - wiltomap

Answer 2

你的子查询包含一个GROUP BY表达式，而外部查询没有。因此，你必须使用一个子查询。

你可以将其内联，但这只会使查询更难阅读。你的查询已经尽可能简单了。

- Tamas Rev

Answer 3

你的方法是正确的 - 使用CTE（公共表达式）驱动的查询更易读，并且如果构建正确，它们可以提供性能优势。

在你的CTE中，不要选择城镇，而是选择gid列。然后将原始表格与其连接起来，就完成了。

WITH tab AS (
    SELECT gid, MAX(status_date) 
    FROM status 
    GROUP BY gid 
    HAVING MAX(status_date) < '2016-01-01'
)
SELECT s.whatever
FROM tab t
    INNER JOIN status s ON t.gid = s.sid
WHERE s.status = '01' 

编辑

非常抱歉，今天早上我匆忙写错了查询语句。现在我有时间深入分析这个问题。

如果性能很重要，那么请按照主键（PK）进行处理。

WITH tab AS (
    SELECT MAX(gid) as ID
    FROM [status]
    WHERE YEAR(status_date) = 2015 AND status = '01'
    GROUP BY town
)
SELECT s.*
FROM tab t INNER JOIN status s ON t.ID = s.gid

只有当status_date与gid同时增长时，此方法才有效。否则，您需要回退到您发布的原始查询并在日期上取最大值。不过，您可以/应该使用 INNER JOIN代替 LEFT JOIN：

WITH tab AS (  
    SELECT town, MAX(status_date) as Latest
    FROM [status]
    WHERE YEAR(status_date) = 2015 AND status = '01'
    GROUP BY town
)
SELECT s.*
FROM tab t INNER JOIN [status] s ON t.town = s.town AND t.Latest = s.status_date

- Bozhidar Stoyneff

---

那么@Gordon Linoff建议的DISTINCT ON呢？ - wiltomap

这是错误的。它包括 86003，这是2015年末的状态02。 - Clodoaldo Neto

你需要根据 gid 和 status_date 进行连接。 - user330315

@ClodoaldoNeto 不是的，@Gordon Linoff 的查询只返回 86002！试一试... - wiltomap

@wiltomap, 我在这里的评论指的是这个答案，而不是戈登的答案。 - Clodoaldo Neto

@ClodoaldoNeto 是的，那我很抱歉！ - wiltomap