如何创建新列计算表内最大日期与当前行日期的差值

问题原因 #

1. 语法报错：你写的SQL子查询放置位置不对，SELECT子句中date字段后直接紧跟子查询，缺少分隔符，也没有将子查询作为标量值嵌入计算逻辑，所以解析器遇到括号时报错。
2. 此前datediff返回0：是因为同级别查询里如果直接用MAX(date)计算，在GROUP BY date的逻辑下，每组的最大日期就是当前组的date，所以差值恒为0。

正确实现代码 #

有两种常用实现方式：

方式1：标量子查询（写法简单，适合小表） #

直接把计算全表最大日期的子查询嵌入DATEDIFF函数中，子查询会返回唯一的最大日期值参与每行的计算：

SELECT
  date,
  DATEDIFF((SELECT MAX(date) FROM mytable), date) AS datediff
FROM mytable
GROUP BY date

方式2：交叉连接关联最大日期（性能更优，适合大表） #

提前计算一次全表最大日期，再和原表关联计算，避免每行都重复执行子查询：

SELECT
  t.date,
  DATEDIFF(m.max_date, t.date) AS datediff
FROM mytable t
CROSS JOIN (SELECT MAX(date) AS max_date FROM mytable) m
GROUP BY t.date, m.max_date

两种写法都可以得到你期望的输出结果。

内容的提问来源于stack exchange，提问作者Rob Rosene