union排序，起别名将两个不同的字段ZCDZ，SCJYDZ 变成同一个别名dz，进行排序；增加一个字段z，进行排序。...

with a as(
select NSRSBH,NSRMC,ZGSWJ_DM,ZGSWSKFJ_DM,SSGLY_DM,FDDBRXM,ZCDZ dz,1 z
from hx_dj.dj_nsrxx nsr
where nsr.zcdz in (select n.zcdz from (select ns.zcdz,count(1) cnt from hx_dj.dj_nsrxx ns where ns.nsrzt_dm = '03'
and ns.kzztdjlx_dm <> '1130'
and ns.zcdz is not null and ns.ZGSWSKFJ_DM LIKE '13609%' group by ns.zcdz
having count(1) > 1) n)
union
select NSRSBH,NSRMC,ZGSWJ_DM,ZGSWSKFJ_DM,SSGLY_DM,FDDBRXM,SCJYDZ dz,2 z
from hx_dj.dj_nsrxx nsr
where nsr.scjydz in (select n.scjydz from (select ns.scjydz,count(1) cnt from hx_dj.dj_nsrxx ns where ns.nsrzt_dm = '03'
and ns.kzztdjlx_dm <> '1130'
and ns.zcdz is not null and ns.ZGSWSKFJ_DM LIKE '13609%' group by ns.scjydz
having count(1) > 1) n)
)

select * from a

order by z,dz

转载于:https://www.cnblogs.com/shenzhichipingguo/p/8629993.html