MySQL动态透视表每日摘要和销售状态

假设我们有以下销售数据表格（sales_data）：

我们需要以日期为行和销售状态为列，生成每日的销售摘要报告。

1. 使用动态SQL语句生成列：

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(CASE WHEN sales_status = ''',
      sales_status,
      ''' THEN sales_count ELSE 0 END) AS ',
      sales_status
    )
  ) INTO @sql
FROM
  sales_data;

这将生成以下SQL语句：

SELECT
  SUM(CASE WHEN sales_status = 'Pending' THEN sales_count ELSE 0 END) AS Pending,
  SUM(CASE WHEN sales_status = 'Shipped' THEN sales_count ELSE 0 END) AS Shipped
FROM
  sales_data;

2. 将列与日期进行分组并生成每日报告：

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(CASE WHEN sales_status = ''',
      sales_status,
      ''' THEN sales_count ELSE 0 END) AS ',
      sales_status
    )
  ) INTO @sql
FROM
  sales_data;

SET @sql = CONCAT(
  'SELECT
     date, ',
  @sql,
  ' FROM
     sales_data
   GROUP BY date'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;

这将生成以下报告：

| date       | Pending | Shipped |
|------------|---------|---------|
| 2021-07-01 | 10      | 15      |
| 2021-07-02 | 5       | 20      |
| 2021-07-03 | 7       | 25      |