可以在MySQL中使用Crosstab/Pivot查询吗?

本文介绍了可以在MySQL中使用Crosstab/Pivot查询吗?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我正在使用MySQL.这是我的桌子

I'm using MySQL. This is table i have

supplier_ID   Item_ID   Date                  Price    QTY
1             1         2012-01-01 00:00:00   500.00   2
1             1         2012-01-03 00:00:00   450.00   10
2             1         2012-01-01 00:00:00   400.00   5
3             1         2012-05-01 00:00:00   500.00   1

我需要一个选择查询，该查询显示一个类似这样的表.

I need a select query showing a table something like this.

supplier_ID      2012-01-01   2012-01-03   2012-05-01
1                500.00(2)    450.00(10)   null
2                400.00(5)    null         null
3                null         null         500.00(1)

推荐答案

您可以使用此查询-

SELECT
  supplier_id,
  MAX(IF(date = '2012-01-01', value, NULL)) AS '2012-01-01',
  MAX(IF(date = '2012-01-03', value, NULL)) AS '2012-01-03',
  MAX(IF(date = '2012-05-01', value, NULL)) AS '2012-05-01'
FROM (
  SELECT supplier_id, DATE(date) date, CONCAT(SUM(price), '(', qty, ')') value FROM supplier
    GROUP BY supplier_id, DATE(date)
    ) t
  GROUP BY supplier_id;

+-------------+------------+------------+------------+
| supplier_id | 2012-01-01 | 2012-01-03 | 2012-05-01 |
+-------------+------------+------------+------------+
|           1 | 500.00(2)  | 450.00(10) | NULL       |
|           2 | 400.00(5)  | NULL       | NULL       |
|           3 | NULL       | NULL       | 500.00(1)  |
+-------------+------------+------------+------------+

它会产生您想要的结果.但是，如果您想动态地进行操作，则请参阅本文自动执行数据透视表查询"- http://www.artfulsoftware.com/infotree/queries.php#523 或此链接-动态数据透视表.

It produces result you want. But if you want to do it dynamically, then have a look at this article 'Automate pivot table queries' - http://www.artfulsoftware.com/infotree/queries.php#523, or this link - Dynamic pivot tables.

这篇关于可以在MySQL中使用Crosstab/Pivot查询吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！