select o.字段单号,o.日期,e1.检验类型 as 维修,e2.检验类型 as 合格,e3.检验类型 as 报废

from AAA o

left join ABC e1 on o.id=e1.id and e1.检验类型='维修'

left join ABC e2 on o.id=e2.id and e2.检验类型='合格'

left join ABC e3 on o.id=e3.id and e3.检验类型='报废'

SELECT * FROM (select ABC.*,AAA.字段单号,AAA.日期 from ABC LEFT JOIN AAA ON AAA.ID=ABC.ID)S

pivot(max(S.字段数量) for S.字段检验类型  in (维修,合格,报废))  as ss