如何使用Group By将触发器启停日志的起止时间列并排展示

你可以使用分组聚合的方式实现行转列合并，以下是兼容绝大多数SQL方言的写法：

SELECT
    job_ID AS JobID,
    PROCEDURE_NAME,
    MAX(CASE WHEN STEP_NAME = 'Start' THEN CAST(DATE_INSERTED AS TIMESTAMP) END) AS START_TIME,
    MAX(CASE WHEN STEP_NAME = 'End' THEN CAST(DATE_INSERTED AS TIMESTAMP) END) AS END_TIME,
    TRIM(SUBSTR(MESSAGE, -12)) AS MSG
FROM CON_FRAMEWORK_LOG
GROUP BY
    job_ID,
    PROCEDURE_NAME,
    TRIM(SUBSTR(MESSAGE, -12))
ORDER BY START_TIME;

逻辑说明 #

• 按job_ID、PROCEDURE_NAME、提取后的MSG三个维度分组，保证同一组内是同一个作业的同一种触发器操作的起止记录
• 用CASE WHEN分别筛选出步骤为Start和End的时间，搭配MAX聚合函数取出对应时间值
• 如果你使用的数据库支持PIVOT语法，也可以用PIVOT实现相同效果，写法更简洁，以Oracle为例：

SELECT * FROM (
    SELECT
        job_ID AS JobID,
        PROCEDURE_NAME,
        CAST(DATE_INSERTED AS TIMESTAMP) AS OP_TIME,
        TRIM(SUBSTR(MESSAGE, -12)) AS MSG,
        STEP_NAME
    FROM CON_FRAMEWORK_LOG
)
PIVOT (
    MAX(OP_TIME) FOR STEP_NAME IN ('Start' AS START_TIME, 'End' AS END_TIME)
)
ORDER BY START_TIME;

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