代码之家 › 专栏 › 技术社区 › monty

PostgreSQL在子查询中为空或合并

postgresql sql

monty · 技术社区 · 7 年前

好吧,我有一个带有时间戳列的表。我必须返回该表中时间戳在给定时间点(=timex)之后,但加上最后一个记录timex的所有记录。

我的方法是对按时间戳排序的条目进行编号。然后再选择timex之后第一条记录的编号(=numx)。并返回数字大于numx-1的所有记录。

测试设置

  CREATE TABLE public.events
  (
    id integer,
    name character varying(50) COLLATE pg_catalog."default",
    happens timestamp without time zone
  );

  INSERT INTO events (id, name, happens)
  VALUES
  (1, 'event 7', '2018-08-01 07:00:00.000000'),
  (2, 'event 6', '2018-08-01 06:00:00.000000'),
  (3, 'event 2', '2018-08-01 02:00:00.000000'),
  (4, 'event 5', '2018-08-01 05:00:00.000000'),
  (5, 'event 1', '2018-08-01 01:00:00.000000'),
  (6, 'event 8', '2018-08-01 08:00:00.000000'),
  (7, 'event 5.2', '2018-08-01 05:00:00.000000'),
  (8, 'event 4', '2018-08-01 04:00:00.000000'),
  (9, 'event 3', '2018-08-01 03:00:00.000000');

我当前的查询

  WITH AllEvents AS (
    SELECT
      ROW_NUMBER() OVER (ORDER BY happens) AS num,
      id,
      name,
      happens
    FROM events
    ORDER BY num
  )
  SELECT * FROM AllEvents ae
  WHERE ae.num >= 
  (
    SELECT Num-1
    FROM AllEvents
    WHERE happens >= '2018-08-01 02:30:00'
    LIMIT 1
  );

查询在“2018-08-01 02:30:00”下运行良好,因为select会找到一个可以返回num的条目。

如果我尝试选择“2018-08-01 17:30:00”,则查询找不到记录,因此返回的num为空,结果是空列表而不是最后一条记录。

我试着用isull或coalesce和max(ae.num)来解决这个问题,但我没法让它工作。

任何提示我都可以完成空检查并返回最后一条记录(这等于max(ae.num)??

2 回复 | 直到 7 年前

Cetin Basoz 7 年前

这不简单吗?:

select * from events
where happens >= 
(select max(happens) from events
    WHERE happens < '2018-08-01 02:30:00');

Gordon Linoff 7 年前

我真的不明白你想要实现的逻辑是什么。所以,可能有一个更简单的方法来实现你想要的。

但是,可以将子查询作为参数放入以修复查询:

WHERE ae.num >= COALESCE((SELECT ae2.Num - 1
                          FROM AllEvents ae2
                          WHERE ae2.happens >= '2018-08-01 02:30:00'
                          LIMIT 1
                         ), ae.num
                        );

您还将注意到我向子查询添加了表别名和限定列名。这是避免使用子查询时出现问题的最佳实践。

推荐文章

Johnny T · 基于当前值的SQL合并表[重复]

6 月前

Kallydi · jdbc无法连接到docker中的postgresql数据库

6 月前

dietzi96 · Pandas DataFrame.to_sql随机和静默地失败,没有错误消息

6 月前

Peter Schofield · 类型转换Postgresql

6 月前

BarÄ±Å UÅaklÄ± · 如何在ON冲突更新中使用CTE中的值

6 月前

Kevin Smeeks · Pyspark JDBC分区读取

6 月前

Micah Armantrout · 如何使用sql脚本将数据插入到代码优先的数据库中?

6 月前

Vanortton · 使用Docker开始应用程序开发还是在开发过程中配置Docker?[关闭]

6 月前

Andrus · 如何在sql中查找第二个匹配项

7 月前

MWood · Postgres jsonpath用于引用单例元素或元素数组

7 月前