with cte as(select distinct * from (select xm=sjrxm,lxfs=sjrlxfs,dz=sjrdz union all select fjrxm,fjrlxfs,fjrdz)t) select c.xm,c.lxfs,收货次数=(select count(1) from tb t where t.sjrxm=c.xm and t.sjrlxfs=c.lxfs and t.sjdz=c.sjdz), 发货次数=(select count(1) from tb t where t.fjrxm=c.xm and t.fjrlxfs=c.lxfs and t.fjdz=c.sjdz),c.dz from cte c
(select max(sjrxm) as ryxm max(sjrlxfs) as lxfs 0 as fjcs count(*) as shcs max(sjdz) as jtdz from table group by sjrxm, sjrlxfs) union (select max(fjrxm) as ryxm max(fjrlxfs) as lxfs count(*) as fhcs 0 as sjcs max(fjdz) as jtdz from table group by fjrxm, fjrlxfs)
select c.xm,c.lxfs,收货次数=(select count(1) from tb t where t.sjrxm=c.xm and t.sjrlxfs=c.lxfs and t.sjdz=c.sjdz),
发货次数=(select count(1) from tb t where t.fjrxm=c.xm and t.fjrlxfs=c.lxfs and t.fjdz=c.sjdz),c.dz from cte c
max(sjrxm) as ryxm
max(sjrlxfs) as lxfs
0 as fjcs
count(*) as shcs
max(sjdz) as jtdz
from table
group by sjrxm, sjrlxfs)
union
(select
max(fjrxm) as ryxm
max(fjrlxfs) as lxfs
count(*) as fhcs
0 as sjcs
max(fjdz) as jtdz
from table
group by fjrxm, fjrlxfs)