sql 数据补齐并排序问题

img


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

DECLARE @a TABLE(code varchar(20),name varchar(10),rate varchar(10))
insert @a SELECT 'TSTMO221130023','压力','100%'
UNION ALL SELECT 'TSTMO221130023','点开机','100%'
UNION ALL SELECT 'TSTMO221130023','DOS','100%'
UNION ALL SELECT 'TSTMO221130023','送检','100%'
UNION ALL SELECT 'TSTMO221130024','压力','100%'
UNION ALL SELECT 'TSTMO221130024','点开机','100%'

SELECT aa.code,aa.name,bb.rate FROM 
(
SELECT * FROM 
(SELECT DISTINCT code FROM @a ) a,
(SELECT 1 ORD,'DOS' NAME 
UNION ALL SELECT 2,'压力'
UNION ALL SELECT 3,'点开机'
UNION ALL SELECT 4,'送检') b 
) aa LEFT JOIN @a bb
ON aa.code = bb.code AND aa.name = bb.name 
ORDER BY aa.code,aa.ord

增加一个工序表，工序表增加一个rank字段用于排序，然后连表查询，select * from 订单表 a inner join 工序表 b on a.工序名称=b.工序名称 order a.生成订单 b.rank