我有一个按周统计所有票证的查询,但我需要将其转换为一个动态的两行报表,将周开始日期移动到列?
这就是我想让它做的。。
我的问题是:
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;
我得到以下结果:
你对我上面的陈述有什么看法吗?
更新:这是我的最新查询感谢uuerdo。这使我可以手动进行每周计算,但如何修复上面的语句以动态地进行计算
1条答案
按热度按时间xdnvmnnf1#
正确的mysql语句是:
这产生了如下正确的输出:
感谢所有的帮助!