sql 补齐数据，并按照要求排序

Question

sql 补齐数据

像图片一样的数据，怎么补齐缺失的工序，就是每个工单都需要有这四个工序，并按照工单，工序 排序（DOS,压力，点开机，送检

）

Answer 1

WITH TEMP AS
(SELECT  T.SCDD        -- 生产订单
       ,T1. GXMC     -- 工序名称
   FROM TABLE T        -- 源表
   JOIN (SELECT 'DOS' AS GXMC UNION ALL SELECT '压力' UNION ALL SELECT '点开机' UNION ALL SELECT '送检')  T1
       ON 1=1
GROUP BY T.SCDD,T1. GXMC)

SELECT T.SCDD,T.GXMC
    FROM TEMP1 T
    LEFT JOIN TABLE T1
        ON T. SCDD=T1.SCDD
      AND T.GXMC=T1.GXMC
    WHERE T1.GXMC IS NULL 
    ORDER BY T.SCDD
         ,CASE WHEN T.GXMC='DOS'   THEN 1
             WHEN T.GXMC='压力'    THEN 2
             WHEN T.GXMC='点开机' THEN 3
             WHEN T.GXMC='送检'    THEN 4 END

这个思路可以提供给题主借鉴哈，可以一起探讨~