qq_27672059 于 2016.09.19 11:13 提问

sql server group by分组 合并 显示表中所以数据

`````` select a.concretename, a.fhsj,* from
(select concretename, convert(varchar(10),fhsj,120)fhsj,
sum(fl)B_fl,(sum(pb_SHI1_3)+sum(pb_SHI1_2))B_pb_SHI1_3,sum(pb_SHI0_5)B_pb_SHI0_5,sum(pb_SHA)B_pb_SHA,(sum(pb_RgSha)+sum(pb_ShiFen))B_pb_ShiFen,'B' B_Pb,
sum(pb_HuanNing)B_pb_HuanNing,sum(pb_ZaoQiang)B_pb_ZaoQiang,sum(pb_ZuJi)B_pb_ZuJi,
0 L_fl,0 L_pb_SHI1_3,0 L_pb_SHI0_5,0 L_pb_SHA,0 L_pb_ShiFen,'L' L_Pb,0 L_pb_HuanNing,0 L_pb_ZaoQiang,0 L_pb_ZuJi,
0 S_fl,0 S_pb_SHI1_3,0 S_pb_SHI0_5,0 S_pb_SHA,0 S_pb_ShiFen,'S' S_Pb,0 S_pb_HuanNing,0 S_pb_ZaoQiang,0 S_pb_ZuJi
from #T   where charindex('B',pbmc)>0 group by convert(varchar(10),fhsj,120),concretename

union all

select concretename,convert(varchar(10),fhsj,120)fhsj,
0 B_fl,0 B_pb_SHI1_3,0 B_pb_SHI0_5,0 B_pb_SHA,0 B_pb_ShiFen,'B' B_Pb,0 B_pb_HuanNing,0 B_pb_ZaoQiang,0 B_pb_ZuJi,
sum(fl)L_fl,(sum(pb_SHI1_3)+sum(pb_SHI1_2))L_pb_SHI1_3,sum(pb_SHI0_5)L_pb_SHI0_5,sum(pb_SHA)L_pb_SHA,(sum(pb_RgSha)+sum(pb_ShiFen))L_pb_ShiFen,'L' L_Pb,
sum(pb_HuanNing)L_pb_HuanNing,sum(pb_ZaoQiang)L_pb_ZaoQiang,sum(pb_ZuJi)L_pb_ZuJi,
0 S_fl,0 S_pb_SHI1_3,0 S_pb_SHI0_5,0 S_pb_SHA,0 S_pb_ShiFen,'S' S_Pb,0 S_pb_HuanNing,0 S_pb_ZaoQiang,0 S_pb_ZuJi
from #T where charindex('L',pbmc)>0  group by convert(varchar(10),fhsj,120) ,concretename

union all

select concretename,convert(varchar(10),fhsj,120)fhsj,
0 B_fl,0 B_pb_SHI1_3,0 B_pb_SHI0_5,0 B_pb_SHA,0 B_pb_ShiFen,'B' B_Pb,0 B_pb_HuanNing,0 B_pb_ZaoQiang,0 B_pb_ZuJi,
0 L_fl,0 L_pb_SHI1_3,0 L_pb_SHI0_5,0 L_pb_SHA,0 L_pb_ShiFen,'L' L_Pb,0 L_pb_HuanNing,0 L_pb_ZaoQiang,0 L_pb_ZuJi,
sum(fl)S_fl,(sum(pb_SHI1_3)+sum(pb_SHI1_2))S_pb_SHI1_3,sum(pb_SHI0_5)S_pb_SHI0_5,sum(pb_SHA)S_pb_SHA,(sum(pb_RgSha)+sum(pb_ShiFen))S_pb_ShiFen,'S' S_Pb,
sum(pb_HuanNing)S_pb_HuanNing,sum(pb_ZaoQiang)S_pb_ZaoQiang,sum(pb_ZuJi)S_pb_ZuJi
from #T  where  charindex('S',pbmc)>0 group by convert(varchar(10),fhsj,120) ,concretename )a   group by a.concretename, a.fhsj

``````

1个回答

shihengzhen101   2016.09.19 11:16

qq_27672059 按a.concretename, a.fhsj两个字段分组数据 但要显示表中所有字段