MySQL查询当日及7天前交易数返回null的解决方法

问题根因 #

你写的子查询中where条件两侧的date_id都属于子查询内部的transactions表，等价于判断date(date_id) = date(date_id) - 7天，这个条件永远不成立，因此子查询全返回null。要关联主表的日期，必须给表加别名区分作用域。

解法1：自关联查询（兼容性最好，适配所有SQL版本） #

给两张相同的transactions表分别起别名区分，用主表的日期减7天关联另一张表的日期，最后过滤掉没有7天前数据的记录即可：

SELECT
  t1.date_id,
  t1.t_count,
  t2.t_count AS t_count_7d_prev
FROM transactions t1
LEFT JOIN transactions t2
  -- 不同数据库日期函数可能略有差异，SQL Server可替换为DATEADD(day,-7,t1.date_id)
  ON t2.date_id = DATE_SUB(t1.date_id, INTERVAL 7 DAY)
WHERE t2.t_count IS NOT NULL -- 过滤掉无7天前对应数据的日期，匹配你的预期输出
ORDER BY t1.date_id DESC

解法2：窗口函数（写法更简洁，支持MySQL8.0+/PostgreSQL/SQL Server等新版数据库） #

直接用LAG偏移函数，按日期排序后取向前偏移7行的交易数即可：

WITH daily_trade AS (
  SELECT
    date_id,
    t_count,
    LAG(t_count,7) OVER(ORDER BY date_id ASC) AS t_count_7d_prev
  FROM transactions
)
SELECT * FROM daily_trade
WHERE t_count_7d_prev IS NOT NULL
ORDER BY date_id DESC

两种写法都可以得到你需要的预期结果。

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