SQL查询问题：获取符合多条件的主表及关联表目标数据

表结构 #

table1 #

table2 #

table3 #

现有SQL代码 #

SELECT 
  t1.tasknum
, t1.description
, t1.refid
, t1.sysdesc
, t2.stepno
, t2.stepdetail
, t2.approvaldate
, MIN(t3.startdate) AS min_date1
, MIN(t3.enddate) AS min_date2

FROM              TABLE1 t1
  LEFT OUTER JOIN TABLE2 t2 ON t1.tasknum = t2.tasknum 
                           AND t2.stepno = (
                                 SELECT MIN(stepno)
                                 FROM TABLE2
                                 WHERE tasknum = t2.tasknum
                               )

  LEFT OUTER JOIN TABLE3 t3 ON t1.refid = t3.id

WHERE t1.sysdesc LIKE 'Apple'

GROUP BY 
  t1.tasknum
, t1.description
, t1.refid
, t1.sysdesc
, t2.stepno
, t2.stepdetail
, t2.approvaldate

表数据 #

table1 #

table2 #

table3 #

查询条件 #

• 筛选table1中sysdesc为'Apple'的所有记录；
• 获取每个tasknum对应的无approvaldate的最小stepno及stepdetail；
• 获取每个tasknum对应的有approvaldate的最大step相关信息；
• 获取每个id对应的最小startdate和enddate。

期望输出样例（tasknum=1） #

问题说明 #

现有SQL仅能获取全局最小stepno的记录，无法满足条件2（无审批日期的最小stepno）和条件3（有审批日期的最大step）的需求，需调整查询逻辑。

修正后的SQL #

WITH step_data AS (
    -- 提取每个tasknum的两类关键step数据
    SELECT 
        tasknum,
        -- 无审批日期的最小stepno及对应详情
        MIN(CASE WHEN approvaldate IS NULL THEN stepno END) AS min_null_approval_stepno,
        MAX(CASE WHEN stepno = MIN(CASE WHEN approvaldate IS NULL THEN stepno END) THEN stepdetail END) AS min_null_approval_stepdetail,
        -- 有审批日期的最大step相关信息
        MAX(CASE WHEN approvaldate IS NOT NULL THEN stepno END) AS max_approval_stepno,
        MAX(CASE WHEN stepno = MAX(CASE WHEN approvaldate IS NOT NULL THEN stepno END) THEN stepdetail END) AS max_approval_stepdetail,
        MAX(CASE WHEN stepno = MAX(CASE WHEN approvaldate IS NOT NULL THEN stepno END) THEN approvaldate END) AS max_approval_date
    FROM table2
    GROUP BY tasknum
),
date_data AS (
    -- 聚合每个id的最小日期数据
    SELECT 
        id,
        MIN(startdate) AS min_startdate,
        MIN(enddate) AS min_enddate
    FROM table3
    GROUP BY id
)
SELECT 
    t1.tasknum,
    t1.description,
    t1.refid,
    t1.sysdesc,
    s.min_null_approval_stepno AS stepno,
    s.min_null_approval_stepdetail AS stepdetail,
    s.max_approval_date AS approvaldate,
    d.min_startdate AS startdate,
    d.min_enddate AS enddate
FROM table1 t1
LEFT JOIN step_data s ON t1.tasknum = s.tasknum
LEFT JOIN date_data d ON t1.refid = d.id
WHERE t1.sysdesc = 'Apple';

逻辑说明 #

1. step_data CTE：通过条件聚合分别提取每个tasknum下无审批日期的最小stepno及对应详情，还有有审批日期的最大step的详情和审批日期；
2. date_data CTE：聚合获取每个id对应的最小startdate和enddate；
3. 主查询将table1与两个CTE关联，筛选sysdesc为'Apple'的记录，得到符合要求的单行结果。

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