本文介绍了带有数据聚合的 SQL 循环的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我使用的是 MySql 5.5.38 并得到了下表(这只是一个片段，真实的包含约 500 个产品):
I'm using MySql 5.5.38 and got a following table (this is just a snippet, real one contains ~500 products):
+------+-------+----------+-------+--------+
| id | date | product | type | sales |
+------+-------+----------+-------+--------+
| 4513 | 14958 | XXXXXXX | 1 | 3 |
| 4514 | 14958 | XXXXXXX | 2 | 5 |
| 4519 | 14958 | YYYYYYY | 1 | 10 |
| 4531 | 14958 | YYYYYYY | 3 | 150 |
| 4534 | 14959 | ZZZZZZZ | 1 | 5 |
| 4536 | 14959 | ZZZZZZZ | 2 | 5 |
|+------+-------+----------+-------+--------+
我需要从中生成按产品的每日销售额:
from which I need to generate daily sales volume by product:
+-------+----------+----------+----------+
| date | XXXXXXX | YYYYYYY | ZZZZZZZ |
+-------+----------+----------+----------+
| 14958 | 8 | 110 | 0 |
+-------+----------+----------+----------+
| 14959 | 0 | 0 | 10 |
|+------+----------+----------+----------+
结果表必须与上面完全一样 - 产品在列中，天在行中.
The result table has to look exactly like above - with products in columns and days in row.
只是寻找一些指示，而不是有效的解决方案.谢谢，帕维尔.
Just looking for some pointers, not a working solution. Thanks, Pawel.
推荐答案
对于动态聚合，使用 GROUP_CONCAT 与 CONCAT
For dynamic aggregation, use GROUP_CONCAT with CONCAT
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT('SUM(CASE WHEN product= "',
product, '" THEN sales ELSE 0 END) AS '
, product))
INTO @sql
FROM
my_table;
SET @sql = CONCAT('SELECT date, ', @sql, '
FROM my_table
GROUP BY date');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
使用条件case和group by(静态)
SELECT s.date,
SUM(
CASE
WHEN s.product='XXXXXXX'
THEN s.sales
ELSE 0
END
) AS 'XXXXXXX',
SUM(
CASE
WHEN s.product='YYYYYYY'
THEN s.sales
ELSE 0
END
) AS 'YYYYYYY',
SUM(
CASE
WHEN s.product='ZZZZZZZ'
THEN s.sales
ELSE 0
END
) AS 'ZZZZZZZ'
FROM my_table s
GROUP BY s.date;
这篇关于带有数据聚合的 SQL 循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！