MSSQL含MAX聚合的子查询转换为Informix语法方案

原MSSQL查询逻辑 #

原有运行在MSSQL环境的查询用于按订单号分组，获取每个订单对应的最大日期，以及该最大日期下的最大时间，用于校验数据是否发生变更，在MSSQL中运行稳定、执行效率高，语句如下：

select
    c1.auftrnr
    ,MAX(DATUM) as Datum
    ,(select MAX(Zeit) from kaufedit where auftrnr=c1.auftrnr and datum=max(c1.datum)) as Zeit
from kaufedit c1
group by c1.auftrnr
order by c1.auftrnr

环境约束 #

当前使用的数据库环境及限制如下：

• Informix版本：12.10.FC14WE
• 字段类型不可修改：datum字段类型为date(10)，zeit字段类型为char(5)
• 此前尝试的日期时间拼接写法未达预期，尝试的语句如下：

select extend(datum, year to second)+(zeit - DATETIME(00:00) hour to minute) as datumzeit from kaufedit

转换失败原因 #

原MSSQL语法中，子查询的过滤条件直接引用外层分组计算得到的max(c1.datum)聚合值，Informix不支持在嵌套子查询的WHERE条件中直接引用外层聚合函数的计算结果，会触发语法错误；另外此前的时间拼接语句未对char类型的zeit字段做显式类型转换，Informix无法自动识别char值为时间类型做间隔运算，导致执行失败。

Informix兼容写法 #

基础功能实现（和原MSSQL逻辑完全一致） #

通过子查询先预计算每个订单号对应的最大日期，再关联原表取该日期下的最大时间，完全避开Informix的语法限制，执行效率和原MSSQL语句相当：

SELECT
    t1.auftrnr,
    t1.max_datum AS Datum,
    MAX(t2.zeit) AS Zeit
FROM (
    SELECT auftrnr, MAX(datum) AS max_datum
    FROM kaufedit
    GROUP BY auftrnr
) t1
INNER JOIN kaufedit t2
    ON t1.auftrnr = t2.auftrnr
    AND t1.max_datum = t2.datum
GROUP BY t1.auftrnr, t1.max_datum
ORDER BY t1.auftrnr

带时间戳拼接的扩展写法 #

如果需要合并日期和时间字段生成完整时间戳，只需要对zeit字段做显式类型转换再做运算即可，修正后的完整语句如下：

SELECT
    res.auftrnr,
    res.Datum,
    res.Zeit,
    EXTEND(res.Datum, YEAR TO SECOND) + (res.Zeit::DATETIME HOUR TO MINUTE - DATETIME(00:00) HOUR TO MINUTE) AS datumzeit
FROM (
    SELECT
        t1.auftrnr,
        t1.max_datum AS Datum,
        MAX(t2.zeit) AS Zeit
    FROM (
        SELECT auftrnr, MAX(datum) AS max_datum
        FROM kaufedit
        GROUP BY auftrnr
    ) t1
    INNER JOIN kaufedit t2
        ON t1.auftrnr = t2.auftrnr
        AND t1.max_datum = t2.datum
    GROUP BY t1.auftrnr, t1.max_datum
) res
ORDER BY res.auftrnr

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