PostgreSQL查询最旧的日期 [英] PostgreSQL query where date oldest

问题描述

我在Postgres中有一个名为 calendar 的表，我试图查询存储在year = 2013处的第一个日期值。

I have a table in Postgres called calendar and I am trying to query the first date value stored where year=2013.

我写了下面的查询，该查询有效，但是我想知道我是否可以查询第一个日期而不是仅获取所有数据并将其限制为一个？

I have written the following query which works, however I am wondering if I can query the first date instead of just getting all data and limiting just to one?

SELECT date FROM calendar WHERE year=2013 ORDER BY date ASC LIMIT 1

推荐答案

        -- the schema
DROP SCHEMA lutser CASCADE;
CREATE SCHEMA lutser;
SET search_path='lutser';

        -- the table
CREATE TABLE years
  ( zdate DATE NOT NULL PRIMARY KEY
  -- omitting a separate "year" field
  -- , since it can be derived from a date.
  -- ... ...
  );
        -- the data
INSERT INTO years(zdate)
SELECT gs
FROM generate_series( '2012-1-1' , '2014-12-31', '7 days'::interval) gs
        ;

        -- the query
SELECT zdate
FROM years yy
WHERE date_trunc( 'year' , yy.zdate) = '2014-01-01'::date
AND NOT EXISTS (
        SELECT *
        FROM years nx
        WHERE  date_trunc( 'year' , nx.zdate) = '2014-01-01'::date
        AND nx.zdate < yy.zdate
        )
        ;

        -- the same query using a CTE
WITH this AS (
        SELECT zdate
        FROM years yy
        WHERE date_trunc( 'year' , yy.zdate) = '2014-01-01'::date
        )
SELECT th.zdate
FROM this th
WHERE NOT EXISTS (
        SELECT *
        FROM this nx
        WHERE nx.zdate < th.zdate
        )
        ;


\q
        -- the result
DROP SCHEMA
CREATE SCHEMA
SET
CREATE TABLE
INSERT 0 157
   zdate
------------
 2014-01-05
(1 row)

这篇关于PostgreSQL查询最旧的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！