mysql pivot视图动态

dtcbnfnu 于 2021-06-24 发布在 Mysql

关注(0)|答案(1)|浏览(235)

我有一张像这样的table

+-----------------------------------+-------------------------------------------+-------------+
| warehouse                         | item_code                                 | stock_value |
+-----------------------------------+-------------------------------------------+-------------+
| 103-VAN DXB- U56403 NADEEM - DLTL | Alcatel 1054D 1054E BLACK CHARCOAL GREY   | 500         |
+-----------------------------------+-------------------------------------------+-------------+
| 103-VAN DXB- U56403 NADEEM - DLTL | Alcatel 1054D 1054E BLACK+PURE WHITE      | 15000       |
+-----------------------------------+-------------------------------------------+-------------+
| 100-Main Warehouse - Nahda - DLTL | Alcatel 2008D WHITE+METAL SILVER DUAL SIM | 45643       |
+-----------------------------------+-------------------------------------------+-------------+
| 100-Main Warehouse - Nahda - DLTL | Alcatel 2008D-BLACK METAL SILVER DUAL SIM | 4544        |
+-----------------------------------+-------------------------------------------+-------------+

http://www.sqlfiddle.com/#!9/c4fe92/1/0号
列仓库名称不是固定的。那么我如何创建一个如下所示的透视表呢

+-------------------------------------------+----------------+--------+----------------+-------------+
| item                                      | Main Warehouse | NADEEM | total quantity | total value |
+-------------------------------------------+----------------+--------+----------------+-------------+
| Alcatel 1054D 1054E BLACK CHARCOAL GREY   | 0              | 1      | 1              | 500         |
+-------------------------------------------+----------------+--------+----------------+-------------+
| Alcatel 1054D 1054E BLACK+PURE WHITE      | 0              | 1      | 1              | 1500        |
+-------------------------------------------+----------------+--------+----------------+-------------+
| Alcatel 2008D WHITE+METAL SILVER DUAL SIM | 1              | 0      | 1              | 45643       |
+-------------------------------------------+----------------+--------+----------------+-------------+
| Alcatel 2008D-BLACK METAL SILVER DUAL SIM | 1              | 0      | 1              | 4544        |
+-------------------------------------------+----------------+--------+----------------+-------------+

mysql pivot pivot-table

来源：https://stackoverflow.com/questions/48263277/mysql-pivot-view-dynamic

1条答案

按热度按时间

uhry853o1#

假设您要从仓库字段中解析所需的信息，下面是一种方法：

SELECT item_code, 
  IF(LOCATE('Main Warehouse', warehouse), 1, 0) AS `Main Warehouse`, 
  IF(LOCATE('NADEEM', warehouse), 1, 0) AS `NADEEM`, 
  COUNT(item_code) AS total_quantity
FROM some_table
GROUP BY item_code

赞(0）回复(0）举报 2021-06-24

我来回答

mysql pivot视图动态

1条答案

相关问题

热门标签

最新问答