mysql 中关于统计的SQL语句怎么写

Question

[img]http://dl.iteye.com/upload/attachment/472291/162b1ac4-5083-332c-8b96-aab0dfbbc2f3.gif[/img]
如图所示的一个表的数据

我需要的结果如下：

customerId type orderNum shkd srmb ahkd armb shkd_2 srmb_2 ahkd_2 armb_2
113 0 1 2000 900 2000 800 0 0 0 0
116 0 2 4000 900 3300 900 0 0 0 0
430 2 3 350 698 300 600 1100 600 0 0
433 1 1 1000 500 0 0 0 0 0 0
603 1 1 300 600 0 0 0 0 0 0

请问sql 语句怎么写才可以得到这种结果，谢谢

也就是把customerId中重复的记录，并且 type 不相同的，合并为一个记录

Answer 1

首先要确定你重复的记录最多有多少条,我这里假设最多有两条重复customerid的记录
select
customerId,
case when count(*)>=2 then 2 else max(type) end as type,
sum(orderNum) as orderNum,
SUBSTRING_INDEX(group_concat(shkd),',',1) as shkd,
SUBSTRING_INDEX(group_concat(srmb),',',1) as srmb,
SUBSTRING_INDEX(group_concat(ahkd),',',1) as ahkd,
SUBSTRING_INDEX(group_concat(armb),',',1) as armb,
SUBSTRING_INDEX(group_concat(shkd),',',-1) as shkd2,
SUBSTRING_INDEX(group_concat(srmb),',',-1) as srmb2,
SUBSTRING_INDEX(group_concat(ahkd),',',-1) as ahkd2,
SUBSTRING_INDEX(group_concat(armb),',',-1) as armb2
from tableName group by customerId