Sybase派生表ORDER BY使用及按维度取首行的实现咨询

问题描述 #

• 编写三个独立派生表查询，分别针对dimension为'C'、'D'、'H'的数据，每个派生表用TOP 1获取按togo升序的首行后用UNION合并，但启用派生表内的ORDER BY时出错，注释后格式正确但数据不符合预期（未按togo排序）。
• 尝试合并三个维度数据查询，按dimension和togo升序排序，但不知道如何获取每个维度的首行，且各维度togo单位不同，无法统一排序。

原查询代码1 #

SELECT * FROM (SELECT TOP 1 
ac_registr,
event,
CASE 
WHEN dimension = "C" THEN "Cycles"
END AS "dimension",
togo AS "togo cycles",
CEILING (0) AS "togo days",
FLOOR (0) AS "togo hours",
absolute_due_at_ac AS "Due at cycles",
CONVERT( char(10), 0) AS "Due at date",
FLOOR (0) AS "Due at hours",
CONVERT( char(10), dateadd(day, expected_date, 'DEC 31 1971'), 104) AS "expected_date_of_perform",
event_display
FROM forecast 
WHERE ac_registr IN ('HEU')        
      AND dimension = 'C'
      AND expected_date <= 19669
      AND expected_date <> 0
/*ORDER BY togo ASC*/) C 

UNION

SELECT * FROM (SELECT TOP 1 
ac_registr,
event,
CASE 
WHEN dimension = "D" THEN "Days"
END AS "dimension",
0 AS "togo cycles",
CEILING (togo/1439) AS "togo days",
FLOOR (0) AS "togo hours",
0 AS "Due at cycles",
CONVERT( char(10), dateadd(day, absolute_due_at_ac, 'DEC 31 1971'), 104) AS "Due at date",
FLOOR (0) AS "Due at hours",
CONVERT( char(10), dateadd(day, expected_date, 'DEC 31 1971'), 104) AS "expected_date_of_perform",
event_display
FROM forecast 
WHERE ac_registr IN ('HEU')        
      AND dimension = 'D'
      AND expected_date <= 19669
      AND expected_date <> 0
/*ORDER BY togo ASC*/) D

UNION

SELECT * FROM(SELECT TOP 1 
ac_registr,
event,
CASE 
WHEN dimension = "H" THEN "Hours"
END AS "dimension",
0 AS "togo cycles",
CEILING (0) AS "togo days",
FLOOR (togo/60) AS "togo hours",
0 AS "Due at cycles",
CONVERT( char(10), 0) AS "Due at date",
FLOOR (absolute_due_at_ac/60) AS "Due at hours",
CONVERT( char(10), dateadd(day, expected_date, 'DEC 31 1971'), 104) AS "expected_date_of_perform",
event_display
FROM forecast 
WHERE ac_registr IN ('HEU')        
      AND dimension = 'H'
      AND expected_date <= 19669
      AND expected_date <> 0
/*ORDER BY togo ASC*/) H

原查询代码2 #

SELECT ac_registr,
       event,
       event_type,
       CASE
    WHEN dimension = "C" THEN "Cycles"
    WHEN dimension = "D" THEN "Days"
    WHEN dimension = "H" THEN "Hours"
END AS "dimension",
       togo,
       absolute_due_at_ac,
    CONVERT( char(10), dateadd(day, expected_date, 'DEC 31 1971'), 104) AS "expected_date"
FROM forecast  
WHERE ac_registr IN ('HEU')        
      AND dimension IN ('C', 'D', 'H')      
      AND expected_date <= 19669
      AND expected_date <> 0
ORDER BY dimension, togo ASC

解决方案 #

方法一：修复派生表内的TOP 1 + ORDER BY #

Sybase支持在派生表内使用TOP + ORDER BY，报错大概率是语法细节问题，比如引号混淆、排序字段合法性。修改后的代码如下：

SELECT * FROM (
    SELECT TOP 1 
        ac_registr,
        event,
        CASE WHEN dimension = 'C' THEN 'Cycles' END AS "dimension",
        togo AS "togo cycles",
        CEILING(0) AS "togo days",
        FLOOR(0) AS "togo hours",
        absolute_due_at_ac AS "Due at cycles",
        CONVERT(char(10), 0) AS "Due at date",
        FLOOR(0) AS "Due at hours",
        CONVERT(char(10), dateadd(day, expected_date, 'DEC 31 1971'), 104) AS "expected_date_of_perform",
        event_display
    FROM forecast 
    WHERE ac_registr IN ('HEU')        
          AND dimension = 'C'
          AND expected_date <= 19669
          AND expected_date <> 0
    ORDER BY togo ASC
) C 

UNION ALL -- 用UNION ALL替代UNION，避免无意义的去重开销，需去重则保留UNION
SELECT * FROM (
    SELECT TOP 1 
        ac_registr,
        event,
        CASE WHEN dimension = 'D' THEN 'Days' END AS "dimension",
        0 AS "togo cycles",
        CEILING(togo/1439) AS "togo days",
        FLOOR(0) AS "togo hours",
        0 AS "Due at cycles",
        CONVERT(char(10), dateadd(day, absolute_due_at_ac, 'DEC 31 1971'), 104) AS "Due at date",
        FLOOR(0) AS "Due at hours",
        CONVERT(char(10), dateadd(day, expected_date, 'DEC 31 1971'), 104) AS "expected_date_of_perform",
        event_display
    FROM forecast 
    WHERE ac_registr IN ('HEU')        
          AND dimension = 'D'
          AND expected_date <= 19669
          AND expected_date <> 0
    ORDER BY togo ASC
) D

UNION ALL
SELECT * FROM(
    SELECT TOP 1 
        ac_registr,
        event,
        CASE WHEN dimension = 'H' THEN 'Hours' END AS "dimension",
        0 AS "togo cycles",
        CEILING(0) AS "togo days",
        FLOOR(togo/60) AS "togo hours",
        0 AS "Due at cycles",
        CONVERT(char(10), 0) AS "Due at date",
        FLOOR(absolute_due_at_ac/60) AS "Due at hours",
        CONVERT(char(10), dateadd(day, expected_date, 'DEC 31 1971'), 104) AS "expected_date_of_perform",
        event_display
    FROM forecast 
    WHERE ac_registr IN ('HEU')        
          AND dimension = 'H'
          AND expected_date <= 19669
          AND expected_date <> 0
    ORDER BY togo ASC
) H

说明：建议用单引号替代双引号定义字符串，避免与标识符引用混淆；UNION ALL比UNION性能更高，仅当需要合并去重时使用UNION。

方法二：用窗口函数实现分组取首行（更简洁高效） #

使用ROW_NUMBER()窗口函数，按dimension分组，每组内按togo升序排序后取首行，只需一次表扫描，逻辑更清晰：

WITH ranked_forecast AS (
    SELECT 
        ac_registr,
        event,
        CASE
            WHEN dimension = 'C' THEN 'Cycles'
            WHEN dimension = 'D' THEN 'Days'
            WHEN dimension = 'H' THEN 'Hours'
        END AS "dimension",
        -- 按维度处理togo相关字段
        CASE WHEN dimension = 'C' THEN togo ELSE 0 END AS "togo cycles",
        CASE WHEN dimension = 'D' THEN CEILING(togo/1439) ELSE 0 END AS "togo days",
        CASE WHEN dimension = 'H' THEN FLOOR(togo/60) ELSE 0 END AS "togo hours",
        CASE WHEN dimension = 'C' THEN absolute_due_at_ac ELSE 0 END AS "Due at cycles",
        CASE WHEN dimension = 'D' THEN CONVERT(char(10), dateadd(day, absolute_due_at_ac, 'DEC 31 1971'), 104) ELSE CONVERT(char(10), 0) END AS "Due at date",
        CASE WHEN dimension = 'H' THEN FLOOR(absolute_due_at_ac/60) ELSE 0 END AS "Due at hours",
        CONVERT(char(10), dateadd(day, expected_date, 'DEC 31 1971'), 104) AS "expected_date_of_perform",
        event_display,
        -- 按dimension分组，每组内按togo升序分配排名
        ROW_NUMBER() OVER (PARTITION BY dimension ORDER BY togo ASC) AS rn
    FROM forecast  
    WHERE ac_registr IN ('HEU')        
          AND dimension IN ('C', 'D', 'H')      
          AND expected_date <= 19669
          AND expected_date <> 0
)
SELECT 
    ac_registr,
    event,
    "dimension",
    "togo cycles",
    "togo days",
    "togo hours",
    "Due at cycles",
    "Due at date",
    "Due at hours",
    "expected_date_of_perform",
    event_display
FROM ranked_forecast
WHERE rn = 1;

说明：ROW_NUMBER()会为每个dimension分组内的行生成唯一序号，取rn=1即可得到每组按togo升序的首行数据，完美适配各维度单位不同的场景。

内容的提问来源于stack exchange，提问作者Karel Klíma