MS Access SQL查询实现：各VERTEXID对应前5TIMEID价格缺失补全

• 原查询问题1：RIGHT JOIN后对P1表的字段加WHERE过滤，会过滤掉右连接产生的P1字段为NULL的行，等价于转为INNER JOIN，无法展示VERTEXID缺失TIMEID的行
• 原查询问题2：没有生成指定VERTEXID与目标5个TIMEID的全量笛卡尔积，不会自动为缺失记录的VERTEXID补全对应TIMEID行
• 原查询问题3：没有实现缺失PRICE时取小于当前TIMEID的最近一条记录的逻辑

实现思路 #

1. 先提取符合要求的前5个降序排列的不同TIMEID
2. 生成指定VERTEXID列表与上述TIMEID的笛卡尔积，得到全量的基础行
3. 对每一行基础行，关联PRICES表取同一VERTEXID下小于等于当前TIMEID的最大TIMEID对应的PRICE值

完整SQL代码 #

SELECT 
    T.VERTEXID,
    T.TIMEID,
    (
        SELECT TOP 1 PRICE 
        FROM PRICES 
        WHERE VERTEXID = T.VERTEXID AND TIMEID <= T.TIMEID
        ORDER BY TIMEID DESC
    ) AS PRICE
FROM 
    (
        SELECT V.VERTEXID, P3.TIMEID
        FROM 
            (
                SELECT DISTINCT TOP 5 TIMEID
                FROM PRICES
                WHERE TIMEID <= 20191231
                ORDER BY TIMEID DESC
            ) P3,
            (
                SELECT DISTINCT VERTEXID
                FROM PRICES
                WHERE VERTEXID IN (6001, 6002)
            ) V
    ) T
ORDER BY T.VERTEXID, T.TIMEID DESC

语法说明 #

• 内层子查询P3复用原有逻辑，取到前5个符合要求的TIMEID
• 子查询V提取需要过滤的VERTEXID列表，两个子查询做笛卡尔积，即可得到每个VERTEXID对应5个TIMEID的全量10行基础数据
• 最外层的关联子查询会为每一行基础数据，查找对应VERTEXID下小于等于当前TIMEID的最近一条PRICE，没有匹配记录时会自动返回NULL
• 该写法完全兼容MS Access SQL语法，可直接运行

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