MySQL在同一张表中枢轴 [英] MySQL pivot in same table

问题描述

MySQL在具有动态内容的同一表中枢轴

MySQL pivot in same table with dynamic content

创建表格代码

CREATE TABLE `product_table` (
    `id` INT(10) NOT NULL,
    `pdate` DATE NULL DEFAULT NULL,
    `product` VARCHAR(50) NULL DEFAULT NULL,
    `counts` VARCHAR(50) NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

我的表格结构如下

+----+------------+---------+--------+
| id |   pdate    | product | counts |
+----+------------+---------+--------+
|  1 | 2015-10-12 | BOX     |     74 |
|  2 | 2015-10-12 | SHOE    |     35 |
|  3 | 2015-10-12 | PEN     |     38 |
|  4 | 2015-10-12 | WATCH   |     36 |
|  5 | 2015-10-13 | BOX     |     36 |
|  6 | 2015-10-13 | SHOE    |     80 |
|  7 | 2015-10-13 | PEN     |     70 |
|  8 | 2015-10-13 | WATCH   |     73 |
+----+------------+---------+--------+

我想使用这种格式的报告

+---------+------------+------------+
| product | 2015-10-12 | 2015-10-13 |
+---------+------------+------------+
| BOX     |         74 |         36 |
| SHOE    |         35 |         80 |
| PEN     |         38 |         70 |
| WATCH   |         36 |         73 |
+---------+------------+------------+

到目前为止我尝试过的事情

what i tried so far

select 
    d.p product,
    (select date(p.pdate) from product_table p where date(p.pdate)=d.dt and p.product = d.p ) date,
    (select p.counts from product_table p where date(p.pdate)=d.dt and p.product = d.p ) cnt
from
(select pt.product p,date(pt.pdate) dt from product_table pt group by pt.product,date(pt.pdate) ) as d
group by product

推荐答案

不幸的是，MySQL没有实现表透视.因此，有一个构建动态查询的解决方法，这是我的示例:

Unfortunately MySQL does not have implemented table pivoting. So there is workaround with building a dynamic query, here is my example:

SELECT 
GROUP_CONCAT(DISTINCT(
     CONCAT(
        'MAX(
            IF(pt.pdate =  \'', pdate, '\', pt.counts, null)
         ) AS \'' , pdate, '\''
     )
   )
) INTO @dates FROM product_table;

SET @query = CONCAT('SELECT product, ', @dates, ' FROM product_table pt   GROUP BY product');

PREPARE stmt FROM @query;
EXECUTE stmt;

请注意，如果表中的日期很多，可能会很慢

Please note that if you have a lot of dates in your table it may be very slow

这篇关于MySQL在同一张表中枢轴的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！