SQLite中如何在GROUP BY之前执行ORDER BY实现两表关联查询

问题原因 #

1. 语法顺序错误：SQL执行顺序要求GROUP BY必须放在ORDER BY之前，原语句顺序写反会直接触发语法报错。
2. 不符合SQL分组规范：默认开启ONLY_FULL_GROUP_BY校验的数据库（MySQL 5.7+、PostgreSQL、Oracle等）不允许SELECT子句中出现既不属于分组字段、也没有被聚合函数包裹的字段，原语句中t1.*、t2.LEVEL都不符合要求，会执行失败。
3. 逻辑不符合需求：单条t1记录的TIME_ADD可能落在多个t2的时间区间内，直接JOIN会出现同一条t1记录匹配多条t2记录的情况，需要额外逻辑筛选出唯一匹配行。

修复后的查询语句 #

方案1：支持窗口函数的数据库（MySQL 8.0+、PostgreSQL、Spark SQL等） #

用ROW_NUMBER()窗口函数筛选每个t1记录匹配到的t2中TIME_END最小的行，完全匹配预期输出：

SELECT ID, TIME_ADD, LEVEL
FROM (
    SELECT 
        t1.ID,
        t1.TIME_ADD,
        t2.LEVEL,
        ROW_NUMBER() OVER (PARTITION BY t1.ID, t1.TIME_ADD ORDER BY t2.TIME_END ASC) AS rn
    FROM t1 
    INNER JOIN t2 
        ON t2.TIME_START < t1.TIME_ADD AND t2.TIME_END >= t1.TIME_ADD 
    WHERE t1.TIME_ADD >= '1620837000611' AND t1.TIME_ADD <= '1620882862117'
) t
WHERE rn = 1
ORDER BY TIME_ADD ASC;

方案2：不支持窗口函数的低版本数据库（如MySQL 5.6及以下） #

用聚合关联的方式实现相同效果：

SELECT t1.*, t2.LEVEL
FROM t1
INNER JOIN t2 
    ON t2.TIME_START < t1.TIME_ADD AND t2.TIME_END >= t1.TIME_ADD
INNER JOIN (
    SELECT t1.ID, MIN(t2.TIME_END) AS min_end
    FROM t1
    INNER JOIN t2 ON t2.TIME_START < t1.TIME_ADD AND t2.TIME_END >= t1.TIME_ADD
    WHERE t1.TIME_ADD >= '1620837000611' AND t1.TIME_ADD <= '1620882862117'
    GROUP BY t1.ID
) AS t_min ON t1.ID = t_min.ID AND t2.TIME_END = t_min.min_end
WHERE t1.TIME_ADD >= '1620837000611' AND t1.TIME_ADD <= '1620882862117'
ORDER BY t1.TIME_ADD ASC;

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