sql - 格式化包含多个嵌套 SELECTS 的 CASE 的更好方法

Question

我想这样做:

SELECT (EXTRACT(epoch FROM (
case when 
    (select date from history where statut='X' and id=6 order by date desc limit 1) 
     is null then now() 
  else 
    (select date from history where statut='X' and id=6 order by date desc limit 1) end)
- case when 
    (select date from history where statut in ('Y', 'U') and id=6 order by date desc limit 1) 
     is null then now() 
else 
 (select date from history where statut in ('Y', 'U')  and id=6 order by date desc limit 1) 
 end 
)/3600)

此请求在某些状态的历史修改日期中搜索，减去它，并返回以小时为单位的差异。如果找不到行，则应将其替换为 0。

它有效，但我们都可以看到它有多丑。

有没有办法美化我的查询？

Answer 1

一个简化:

case when X is null
  then Y
  else X
end

可以写成:

coalesce(X, Y)

这会将您的查询减少为:

SELECT (
  EXTRACT(epoch FROM 
    coalesce((select date from history where statut='X' and id=6 order by date desc limit 1), now())
     -
    coalesce((select date from history where statut in ('Y', 'U') and id=6 order by date desc limit 1), now())
  ) / 3600
)

另一个简化:

select X from T order by X desc limit 1

只是:

select max(X) from T

产量:

SELECT (
  EXTRACT(epoch FROM 
    coalesce((select max(date) from history where statut='X' and id=6), now())
     -
    coalesce((select max(date) from history where statut in ('Y', 'U') and id=6), now())
  ) / 3600
)