特殊的行转列需求

最新推荐文章于 2022-08-25 18:08:29 发布

jgmydsai

最新推荐文章于 2022-08-25 18:08:29 发布

阅读量909

点赞数

本文链接：https://blog.csdn.net/jgmydsai/article/details/42143339

版权

mysql> select * from ddd;
+------+------+------+------+
| stat | yi   | er   | san  |
+------+------+------+------+
| a    |    1 |    2 |    3 |
| a    |    4 |    5 |    6 |
| b    |   11 |   22 |    3 |
| b    |    5 |   61 |   82 |
+------+------+------+------+
4 rows in set (0.00 sec)

把上面的数据行转列，结果如下

+------+------+------+------+------+------+------+------+------+------+
| stat | c1   | c2   | c3   | c4   | c5   | c6   | c7   | c8   | c9   |
+------+------+------+------+------+------+------+------+------+------+
| a    |    1 |    2 |    3 |    4 |    5 |    6 | NULL | NULL | NULL |
| b    |    3 |    5 |   11 |   22 |   61 |   82 | NULL | NULL | NULL |
+------+------+------+------+------+------+------+------+------+------+
2 rows in set (0.01 sec)

这种需求在oracle中比较容易实现，因为oracle中有row_number可以生成序号。mysql中就只有另找方法了

set @rn=0;
set @stat='';
select stat,
max(case when rn = 1 then yi end) as c1,
max(case when rn = 2 then yi end) as c2,
max(case when rn = 3 then yi end) as c3,
max(case when rn = 4 then yi end) as c4,
max(case when rn = 5 then yi end) as c5,
max(case when rn = 6 then yi end) as c6,
max(case when rn = 7 then yi end) as c7,
max(case when rn = 8 then yi end) as c8,
max(case when rn = 9 then yi end) as c9
from
(
SELECT stat,
       yi,
       IF(@stat = stat, @rn := @rn + 1, @rn := 1) AS rn,
       @stat := stat AS last_stat
from
(
select stat,yi from ddd
union all
select stat,er from ddd
union all
select stat,san from ddd
) as a
order by stat,yi
) as a
group by stat;

通过分组生成的序号来定位，就可以把对应的值转到相应位置了

mysql> set @rn=0;
Query OK, 0 rows affected (0.00 sec)

mysql> set @stat='';
Query OK, 0 rows affected (0.00 sec)

mysql> select stat,
    -> max(case when rn = 1 then yi end) as c1,
    -> max(case when rn = 2 then yi end) as c2,
    -> max(case when rn = 3 then yi end) as c3,
    -> max(case when rn = 4 then yi end) as c4,
    -> max(case when rn = 5 then yi end) as c5,
    -> max(case when rn = 6 then yi end) as c6,
    -> max(case when rn = 7 then yi end) as c7,
    -> max(case when rn = 8 then yi end) as c8,
    -> max(case when rn = 9 then yi end) as c9
    -> from
    -> (
    -> SELECT stat,
    ->        yi,
    ->        IF(@stat = stat, @rn := @rn + 1, @rn := 1) AS rn,
    ->        @stat := stat AS last_stat
    -> from
    -> (
    -> select stat,yi from ddd
    -> union all
    -> select stat,er from ddd
    -> union all
    -> select stat,san from ddd
    -> ) as a
    -> order by stat,yi
    -> ) as a
    -> group by stat;
+------+------+------+------+------+------+------+------+------+------+
| stat | c1   | c2   | c3   | c4   | c5   | c6   | c7   | c8   | c9   |
+------+------+------+------+------+------+------+------+------+------+
| a    |    1 |    2 |    3 |    4 |    5 |    6 | NULL | NULL | NULL |
| b    |    3 |    5 |   11 |   22 |   61 |   82 | NULL | NULL | NULL |
+------+------+------+------+------+------+------+------+------+------+
2 rows in set (0.01 sec)