Mysql 透视从另一个表添加信息的查询

基本上它是一个简单的枢纽，因为只有5种类型，你可以很容易地添加更多的新的MAX(CASE WHEN行添加到最后SELECT

WITH RECURSIVE dates AS 
(
    SELECT start_date as period, end_date
    FROM progetti
    WHERE id = 1
    UNION ALL
    SELECT DATE_ADD(period, INTERVAL 1 MONTH), end_date
    FROM dates 
    WHERE period < end_date - INTERVAL 1 MONTH
), CTE aS (
SELECT DATE_FORMAT(d.period, '%m-%Y') AS period, COALESCE(total, 0) AS total, COALESCE(s.tipo,0) AS tipo
FROM dates d
LEFT JOIN (
  SELECT DATE_FORMAT(created_on, '%m-%Y') as 'period', 
  COUNT(items_header.id) as 'total' ,
  items_header.tipo 
  FROM items_header  
  WHERE progetto=1
  GROUP BY DATE_FORMAT(created_on, '%m-%Y'), items_header.tipo
) s on s.period = DATE_FORMAT(d.period, '%m-%Y'))
  SELECT 
  period
    ,MAX(CASE WHEN tipo = 1 THEN total ELSE 0 END) tipo_1
  ,MAX(CASE WHEN tipo = 2 THEN total ELSE 0 END) tipo_2
  ,MAX(CASE WHEN tipo = 3 THEN total ELSE 0 END) tipo_3
  ,MAX(CASE WHEN tipo = 4 THEN total ELSE 0 END) tipo_4
FROM CTE 
GROUP BY period
ORDER BY period

fiddle个