MySQL 交叉表查询，动态显示当前周和过去 12 周 [英] MySQL Cross-tab query that displays Current Week, plus Past 12 Weeks Dynamically

问题描述

我有一个按周计算所有票证的查询，但我需要将其转换为动态的两行报告，将周开始日期移至列?

I have a query that counts all tickets by weeks, but I need to convert it to a dynamic two-row report that moves the week-beginning dates to columns?

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

Here is what I'm trying to get it to do..

这是我的查询:

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

它显示星期和倒计时页面，分为两组:

It displays the weeks & counts down the page, for two groups:

当我尝试这个语句时:

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;

我得到以下结果:

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

Any ideas on what I'm doing wrong in my statement above?

更新:感谢 Uueerdo，这是我更新的查询.这给了我手动每周计算，但我如何修复上面的语句以动态地执行它?:

UPDATE: Here is my updated query thanks to Uueerdo. This gives me the weekly calculation MANUALLY, but how do I fix the Statement above to do it DYNAMICALLY? :

推荐答案

正确的 MySQL 语句是:

The correct MySQL Statement was:

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;

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

This produced the correct output as follows:

感谢所有帮助过的人！

这篇关于MySQL 交叉表查询，动态显示当前周和过去 12 周的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！