SQL——字段分组合并

解决方案

MySQL

group_concat函数

group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])

eg: 

select id,group_concat(distinct name) from aa group by id;  

SQL Server

SELECT id, 
         [name] = stuff((
                 SELECT ',' + [name] 
                 FROM temp t 
                 WHERE t.id = temp.id 
                 FOR xml path('')) , 1 , 1 , '') 
FROM temp 

参考文章

group_concat函数详解

如何在sql server中使用group_concat进行查询

已标记关键词 清除标记
想要数据 a.concretename, a.fhsj 是相同的 * 里数据可以不相同 所以 group by 还是分好多条 怎么按fhsj字段相同合为一条数据 ``` 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 ```
©️2020 CSDN 皮肤主题: 1024 设计师:上身试试 返回首页