開始每天記錄一下自己遇到的一些問題，養(yǎng)成一個習慣

日常數(shù)據(jù)統(tǒng)計中經(jīng)常會出現(xiàn)要統(tǒng)計一個部門在一段時間時間范圍內(nèi)的數(shù)據(jù)，會出現(xiàn)如下的這類 報表

image

那么要如何進行統(tǒng)計這類 報表 的數(shù)據(jù)呢？

mysql 動態(tài)列頭生成與統(tǒng)計

-- 由于動態(tài)列頭生成的長度超過了 group_concat 的默認長度，所以在統(tǒng)計之前要設置 group_concat 的連接長度
-- show variables like 'group_concat_max_len';
-- show variables like 'concat_max_len';
-- SET SESSION group_concat_max_len=1024000;

-- 由于數(shù)據(jù)庫中不一定所有的部門與日期對應的數(shù)據(jù)都有，所有通過語句生成左關聯(lián)表，保障數(shù)據(jù)的完整性。
-- 查詢生成日歷的左關聯(lián)數(shù)據(jù)
select * from maohuanhuan_temp_count;
-- 1. 生成做關聯(lián)模板
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. 組裝動態(tài)列
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. 查詢時間范圍內(nèi)的數(shù)據(jù)統(tǒng)計
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;

第一次寫文章有點粗糙，后續(xù)不斷改進。