时间戳

首页 > 解决方案 > 将 SQL 表透视到 N 列

mysql - 将 SQL 表透视到 N 列

问题描述

我有一个表 t1，其中 3 列显示每天每种产品的销售量，表中的产品是动态的，稍后会在表中添加新产品（A、B、C、...）。我需要将表 t1 旋转为{日期，产品 A 的销售数量，产品 B 的销售数量，产品 C 的销售数量，等等..}

如果产品是固定的，即案例 A、B、C，我可以使用案例语句编写查询，但我需要一个动态解决方案。

---------------
-- INPUT DATA
-- table t1
-- date     product_name   quantity_sold
-- 1/1/2013    A           100
-- 1/1/2013    B           200
-- 1/1/2013    C           300
-- 1/2/2013    A           101
-- 1/2/2013    C           301
-- 1/3/2013    A           102
-- 1/3/2013    B           202
-- 1/3/2013    C           302


-- DESIRED OUTPUT
-- date     qty_prod_a  qty_prod_b  qty_prod_c
-- 1/1/2013  100        200         300
-- 1/2/2013  101        0           301
-- 1/3/2013  102        202         302

标签： mysqlsqlgroup-bypivotdynamic-sql

解决方案

正如 P.Salmon 所评论的，您需要动态 sql 来实现这一点。这意味着动态构建查询字符串，然后执行它。

这是一个典型的解决方案：

set @q = null;
select
    group_concat(distinct
        concat(
            'sum(case when product_name = ''', 
            product_name, 
            ''' then quantity_sold else 0 end) as `qty_prod_', 
            lower(product_name),
            '`'
        )
) into @q
from t1;

set @q = concat(
    'select 
        date,
        ', @q, '
    from t1
    group by date
    order by date'
);

prepare stmt from @q;
execute stmt;
deallocate prepare stmt;

DB Fiddle 上的演示：

日期 | 数量_prod_a | 数量_prod_b | 数量_prod_c
:--------- | ---------: | ---------: | ---------:
2013-01-01 | 100 | 200 | 300
2013-01-02 | 101 | 0 | 301
2013-01-03 | 102 | 202 | 302

推荐阅读