id,zhiliangdengjiid,type1,wuliaoid,danhao,lingliao,type2
select * from (select a.id,a.zhiliangdengjiid,a.type1,b.wuliaoid,b.danhao,b.lingliao,b.type2 FROM (
SELECT id,zhiliangdengjiid,‘FG‘ as type1 FROM [ZJHJDB].[dbo].[HJ_bomdanhead] a WHERE a.ID=(SELECT TOP 1 ID from [ZJHJDB].[dbo].[HJ_bomdanhead] WHERE a.zhiliangdengjiid=zhiliangdengjiid ORDER BY status asc) AND LEN(a.zhiliangdengjiid)>1
) as a
INNER JOIN (
SELECT [id],[wuliaoid],[danhao],[lingliao],(CASE WHEN lingliao=-1 THEN ‘P‘ ELSE ‘M‘ END) AS type2 FROM [ZJHJDB].[dbo].[HJ_bomdan2]
) as b ON a.id=b.id) as aa
union all
select * from (
SELECT c.id,b.zhiliangdengjiid,b.type1,c.wuliaoid,c.danhao,c.lingliao,c.type2 FROM
(SELECT [id],[zhiliangdengjiid],‘M‘ as type1 FROM [ZJHJDB].[dbo].[HJ_peifangbiao] AS a WHERE a.ID=
(SELECT TOP 1 ID FROM [ZJHJDB].[dbo].[HJ_peifangbiao] WHERE a.zhiliangdengjiid=zhiliangdengjiid order by status asc) AND len(a.zhiliangdengjiid)>1
) AS b
INNER JOIN (
SELECT id,wuliaoid,danhao,lingliao,(CASE WHEN lingliao=-1 then ‘P‘ else ‘M‘ end) AS type2 FROM [ZJHJDB].[dbo].[HJ_peifangbiao2] WHERE id not in (SELECT id FROM [ZJHJDB].[dbo].[HJ_peifangbiao2] WHERE LEN(wuliaoid)=0)
) AS c on b.ID=c.ID
) as bb
原文：https://www.cnblogs.com/pythonClub/p/14828685.html