sql 数据补齐并排序问题

Question

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

Answer 1

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