Mysql 根据计数按月和年对数据进行分组()

FOR MYSQL个

SET SESSION group_concat_max_len = 1000000;

SET @columns = NULL;

SELECT GROUP_CONCAT(
    DISTINCT CONCAT(
        'SUM(CASE WHEN MONTH = ', MONTH, 
        ' AND YEAR = ', YEAR, 
        ' THEN 1 ELSE 0 END) AS order_count_', 
        MONTHNAME, '_', YEAR
    )
    SEPARATOR ', '
) INTO @columns
FROM (
    SELECT DISTINCT MONTH(created_at) AS MONTH, YEAR(created_at) AS YEAR, 
           MONTHNAME(created_at) AS MONTHNAME
    FROM Orders
) AS months;

SET @sql = CONCAT('SELECT 
                    s.ID AS shop_id,
                    s.shop_name,
                    ', @columns, '
                  FROM
                    shops s
                  LEFT JOIN (
                      SELECT shop_id, 
                             MONTH(created_at) AS MONTH, 
                             YEAR(created_at) AS YEAR
                      FROM orders
                      GROUP BY shop_id, MONTH(created_at), YEAR(created_at)
                  ) o ON s.ID = o.shop_id
                  GROUP BY
                    s.ID, s.shop_name');

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

FOR MSSQL个

DECLARE @columns NVARCHAR(MAX);
DECLARE @sql NVARCHAR(MAX);

-- Generate a comma-separated list of aggregated expressions for each month and year
SELECT @columns = COALESCE(@columns + ', ', '') +
                 'SUM(CASE WHEN MONTH = ' + CONVERT(NVARCHAR(2), MONTH) + 
                 ' AND YEAR = ' + CONVERT(NVARCHAR(4), YEAR) + 
                 ' THEN 1 ELSE 0 END) AS order_count_' + MONTHNAME + '_' + CONVERT(NVARCHAR(4), YEAR)
FROM (
  SELECT DISTINCt MONTH(created_at) AS MONTH, YEAR(created_at) AS YEAR, FORMAT(created_at, 'MMMM') AS MONTHNAME
  FROM Orders
) AS months;

-- Construct the dynamic SQL query
SET @sql = CONCAT('SELECT 
                    s.id AS shop_id,
                    s.shop_name,
                    ', @columns, '
                  FROM
                    Shops s
                  LEFT JOIN (
                      SELECT shop_id, 
                             MONTH(created_at) AS MONTH, 
                             YEAR(created_at) AS YEAR
                      FROM Orders
                      GROUP BY shop_id, MONTH(created_at), YEAR(created_at)
                  ) o ON s.id = o.shop_id
                  GROUP BY
                    s.id, s.shop_name');

-- Execute the dynamic SQL query
EXEC sp_executesql @sql;