复杂sql

1.实现功能，对用户的访问做统计，最近7天的访问人数（1-7连续的），可以按照浏览器，或者其他条件进行分组，合并之后按时间排序
select group_concat(countValue order by sst.countKey),group_concat(countKey order by sst.countKey),brower from (
select DATE_FORMAT(mycreatedate,'%m-%d') as countKey,ifnull(COUNT(pv.create_date), 0) as countValue ,pv.brower from (
SELECT curdate() as mycreatedate
union all
SELECT date_sub(curdate(), interval 1 day) as mycreatedate
union all
SELECT date_sub(curdate(), interval 2 day) as mycreatedate
union all
SELECT date_sub(curdate(), interval 3 day) as mycreatedate
union all
SELECT date_sub(curdate(), interval 4 day) as mycreatedate
union all
SELECT date_sub(curdate(), interval 5 day) as mycreatedate
union all
SELECT date_sub(curdate(), interval 6 day) as mycreatedate
) as total
LEFT JOIN base_log_event pv on DATE_FORMAT(pv.create_date,'%Y-%m-%d')=total.mycreatedate
GROUP BY mycreatedate,pv.brower
) sst
group by sst.brower

注意，如果要对分组合并数据排序group_concat(ss order by ddd)这样写

 

2.统计设备，根据【系统】分组，统计不同【状态】下的设备数量

第一步，先根据系统和状态进行统计

 

SELECT system_id,status,cOUNT(id) AS 'total'  FROM subway_devices
GROUP BY system_id,status

 

 

 

 

 

第二步，行变成列表

SELECT system_id,
        MAX(CASE status  when 1 then total  ELSE 0  END ) AS '在线',
        MAX(case status  when 0 then total  ELSE 0  END ) AS '启动',
        MAX(case status  WHEn 2 then total  ELSE 0  END ) AS '离线'
FROM(
        SELECT system_id,STATUS,cOUNT(id) AS 'total'  FROM subway_devices
       
        GROUP BY system_id,STATUS
) temp
GROUP BY system_id