开始每天记录一下自己遇到的一些问题，养成一个习惯

---

日常数据统计中经常会出现要统计一个部门在一段时间时间范围内的数据，会出现如下的这类 报表

image

那么要如何进行统计这类 报表 的数据呢？

mysql 动态列头生成与统计

-- 由于动态列头生成的长度超过了 group_concat 的默认长度，所以在统计之前要设置 group_concat 的连接长度
-- show variables like 'group_concat_max_len';
-- show variables like 'concat_max_len';
-- SET SESSION group_concat_max_len=1024000;

-- 由于数据库中不一定所有的部门与日期对应的数据都有，所有通过语句生成左关联表，保障数据的完整性。
-- 查询生成日历的左关联数据
select * from maohuanhuan_temp_count;
-- 1. 生成做关联模板
insert into maohuanhuan_temp_count(dept_id,dept_name ,dept_type,count_date)
select dept_id,dept_name,dept_type,dateTab.date 
    from (select * from sys_dept where dept_type between 4 and 98 and enabled=true) dept 
    join (select @num:=@num+1,date_format(adddate('2018-01-01', INTERVAL @num DAY),'%Y-%m-%d') as date from sample,
                (select @num:=0) t where adddate('2018-01-01', INTERVAL @num DAY) <= date_format(curdate(),'%Y-%m-%d') 
                order by date
    ) dateTab 
on 1=1;
-- 2. 生成日期自电脑
select * from maohuanhuan_date;

INSERT INTO maohuanhuan_date ( date ) 
SELECT date 
FROM
    (
    SELECT
        @num := @num + 1,
        date_format( adddate( '2018-01-01', INTERVAL @num DAY ), '%Y-%m-%d' ) AS date 
    FROM
        sample,
        ( SELECT @num := 0 ) t 
    WHERE
        adddate( '2018-01-01', INTERVAL @num DAY ) <= date_format( curdate( ), '%Y-%m-%d' ) 
    ORDER BY
    date 
    ) a

-- 3. 组装动态列
SELECT
 GROUP_CONCAT(DISTINCT
    CONCAT(
        'sum(if(DATE_FORMAT(sample_create,''%Y-%m-%d'') = ''',c.date,''',1,0) ) AS ''',c.date,''''
    )
  )
FROM maohuanhuan_date c;

-- 4. 查询时间范围内的数据统计
select DATE_FORMAT(sample_create,'%Y-%m-%d') dateStr,sample_dept_id,sum(if(DATE_FORMAT(sample_create,'%Y-%m-%d') = '2018-01-01',1,0) ) AS '2018-01-01' 
from sample where sample_create> STR_TO_DATE('2018-01-01','%Y-%m-%d')
group by DATE_FORMAT(sample_create,'%Y-%m-%d'),sample_dept_id;

SET SESSION group_concat_max_len=1024000;
SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
        'sum(if(DATE_FORMAT(sample_create,''%Y-%m-%d'') = ''',c.date,''',1,0) ) AS ''',c.date,''''
    )
  ) into @sql
FROM maohuanhuan_date c where str_to_date(date,'%Y-%m-%d')>=STR_TO_DATE('2019-01-01','%Y-%m-%d');
 
SET @sql = CONCAT('select sys_dept.dept_id,sys_dept.dept_name,', @sql, 
                        ' from sys_dept left join sample on sys_dept.dept_id=sample.sample_dept_id
                                                where sample_create>= STR_TO_DATE(''2019-01-01'',''%Y-%m-%d'')
group by sys_dept.dept_id,sys_dept.dept_name order by sys_dept.dept_id');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

第一次写文章有点粗糙，后续不断改进。