MySQL 交叉表查询动态显示当前周以及过去 12 周

Question

我有一个按周计算所有工单的查询，但我需要将其转换为动态两行报告，将周开始日期移动到列中？

这就是我试图让它做的事情..

这是我的查询:

SELECT td_type, FROM_DAYS(TO_DAYS(ttDate) -MOD(TO_DAYS(ttDate) -2, 7)) AS week_beginning,
   DATE_ADD( DATE(ttDate), INTERVAL (8 - DAYOFWEEK(ttDate)) DAY) week_ending,
   count(*)
FROM v_all_tickets
WHERE extract(year from ttDate) = '2018' AND ttCategory IN ('TT')
GROUP BY td_type, FROM_DAYS(TO_DAYS(ttDate) -MOD(TO_DAYS(ttDate) -2, 7))
ORDER BY FROM_DAYS(TO_DAYS(ttDate) -MOD(TO_DAYS(ttDate) -2, 7)) DESC, td_type

它在页面上显示周数并倒计时，分为两组:

当我尝试这个语句时:

SET @sql_dynamic = (
SELECT
    GROUP_CONCAT( DISTINCT
        CONCAT(
            'COUNT( IF(MONTH(ttDate) = '
            , FROM_DAYS(TO_DAYS(ttDate) -MOD(TO_DAYS(ttDate) -2, 7))
            , ', ttNum,0) ) AS mo_'
            , MONTH(ttDate)
        )
    )
FROM v_all_tickets WHERE extract(year from ttDate) = '2018' AND ttCategory IN ('TT') AND td_type IN ('Fiber','Legacy')
);

SET @sql = CONCAT('SELECT td_type, ', 
          @sql_dynamic, ' 
       FROM v_all_tickets 
       GROUP BY td_type WITH ROLLUP'
   );

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

我得到以下结果:

对于我在上面的陈述中做错了什么有什么想法吗？

更新:这是我更新的查询，感谢 Uueerdo。这为我手动提供了每周计算，但是如何修复上面的语句以动态地进行计算？ :

Answer 1

正确的 MySQL 语句是:

SET SESSION group_concat_max_len = 1000000;
DROP TABLE IF EXISTS tmp_results;

CREATE TEMPORARY TABLE tmp_results AS
SELECT td_type, FROM_DAYS(TO_DAYS(ttDate) -MOD(TO_DAYS(ttDate) -2, 7)) AS ttDate, ttCategory, ttNum, Count(ttNum) tickets
FROM v_closed_tickets
WHERE extract(year from ttDate) = '2018' AND ttCategory IN ('TT')
GROUP BY td_type, ttDate
ORDER BY td_type, ttDate ASC;

SELECT CONCAT('
SELECT td_type, ',tickets_by_dates,'
FROM tmp_results
GROUP BY td_type WITH ROLLUP
'
)
INTO @query
FROM
(
SELECT GROUP_CONCAT(CONCAT('IFNULL(COUNT(CASE WHEN ttDate=''',actual_date,''' THEN tickets END), ''-'') AS "',col_name,'"')) tickets_by_dates
FROM (
SELECT actual_date, DATE_FORMAT(actual_date,'%m/%d') AS col_name
FROM (SELECT DISTINCT ttDate AS actual_date FROM tmp_results) AS dates
) dates_with_col_names
) result;

PREPARE statement FROM @query;
EXECUTE statement;
DEALLOCATE PREPARE statement;

这产生了正确的输出，如下所示:

感谢所有提供帮助的人!