如何按日期分组并根据值创建列？

Question

我有一个两列的订单表：shipping_id引用的运输方法和packed保持订单已被打包的日期。

+-------------+------------+ 
| shipping_id | packed | 
+-------------+------------+ 
| 1   | 2017-05-07 | 
| 1   | 2017-05-06 | 
| 2   | 2017-05-06 | 
| 2   | 2017-05-06 | 
| 3   | 2017-05-05 | 
+-------------+------------+ 

我需要以某种方式组结果的日期packed，把作为细胞的列和值航运类是已经打包的那一天，并与送货方式订单数。这样的事情：

+------------+---+---+---+ 
| date | 1 | 2 | 3 | 
+------------+---+---+---+ 
| 2017-05-05 | 0 | 0 | 1 | 
| 2017-05-06 | 1 | 2 | 0 | 
| 2017-05-07 | 1 | 0 | 0 | 
+------------+---+---+---+ 

这是可能的MySQL？ SQL查询将如何？

谢谢

Answer 1

你不能用香草SQL查询来做到这一点，你将不得不使用存储过程。

对于动态数据透视表，请看MySQL pivot row into dynamic number of columns，但您会发现它并不是很容易使用：默认情况下GROUP_CONCAT()限制为1024字节，您需要制定和使用过程的权利。

---

香草SQL给你查询充其量1。

SQL Fiddle

查询1：

select `shipping_id`, `packed`, count(1) as nb 
from t 
group by `shipping_id`, `packed` 
order by `packed`, `shipping_id` 

Results：

| shipping_id |  packed | nb | 
|-------------|------------|----| 
|   3 | 2017-05-05 | 1 | 
|   1 | 2017-05-06 | 1 | 
|   2 | 2017-05-06 | 2 | 
|   1 | 2017-05-07 | 1 | 

查询2：

SET @sql = NULL 

查询3：

SELECT 
    GROUP_CONCAT(DISTINCT 
    CONCAT(
     'COUNT(IF(shipping_id = ''', 
     shipping_id, 
     ''', 1, NULL)) AS `', 
     shipping_id,'`' 
    ) 
) INTO @sql 
FROM t 

查询4：

SET @sql = CONCAT('SELECT packed, ', @sql, ' FROM t GROUP BY packed ORDER BY `packed`') 

查询5：

PREPARE stmt FROM @sql 

查询6：

EXECUTE stmt 

Results：

|  packed | 1 | 2 | 3 | 
|------------|---|---|---| 
| 2017-05-05 | 0 | 0 | 1 | 
| 2017-05-06 | 1 | 2 | 0 | 
| 2017-05-07 | 1 | 0 | 0 | 

查询7：

DEALLOCATE PREPARE stmt