sql - 包含 WITH 子查询的 PostgreSQL 查询

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') ;

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。