sql //通过分组把组中某一字段拼接 // 拼接 行列转换 //分组统计

 拼接sql
select item,act_id,loc_id,convert(varchar(100),start_date,20)+ '-' +convert(varchar(100),end_date,20) as range_date from test1

   // 分组把 某一列拼接  GROUP_CONCAT()      

select tu.task_id ,GROUP_CONCAT(u.nick_name) users from user u,task_user tu
                    where u.id = tu.user_id and tu.turn is null GROUP BY tu.task_id

   

//分组统计

select r.regionName 区域知,

sum(case when d.sate = 1 then 1 else 0 end) 在线数道量内,

sum(case when d.sate = 0 then 1 else 0 end) 离线容数量

from region r,organize o,devInfo d

where r.regionId = o.regionId

and o.organziedId = d.organzieId

group by r.regionName

        //行列转换                                            
    SELECT * from (                                                            
    select equipment_code,data_time ,site_name,
    SUM(case t.name when  '非甲烷总烃(mg/m3)' then factor_average_value  else 0 end) as '非甲烷总烃(mg/m3)',
    SUM(case t.name when  '大气压(kPa)' then factor_average_value  else 0 end) as '大气压(kPa)',
    SUM(case t.name when  '颗粒物浓度(mg/m3)' then factor_average_value  else 0 end) as '颗粒物浓度(mg/m3)',
    SUM(case t.name when  '油烟浓度(mg/m3)' then factor_average_value  else 0 end) as '油烟浓度(mg/m3)',
    SUM(case t.name when  '烟气温度(℃)' then factor_average_value  else 0 end) as '烟气温度(℃)',
    SUM(case t.name when  '烟气湿度(%)' then factor_average_value  else 0 end) as '烟气湿度(%)',
    SUM(case t.name when  '采样流量(L/min)' then factor_average_value  else 0 end) as '采样流量(L/min)',
    SUM(case t.name when  '净化器工作电流(A)' then factor_average_value  else 0 end) as '净化器工作电流(A)',
    SUM(case t.name when  '排风机工作电流(A)' then factor_average_value  else 0 end) as '排风机工作电流(A)',
    SUM(case t.name when  '时间' then equipment_code  else 0 end) as '时间'
    from 
    (SELECT h.data_time,f.`name`,h.factor_average_value ,s.equipment_code,s.site_name from fume_hour_data  h left join fume_factor f on
    h.factor_id =f.id
                                                                    left join fume_site s on h.site_id =s.id) t
                                                                    

    group by equipment_code ,data_time ORDER BY data_time DESC
) q
GROUP BY site_name