动态行转列sql

最新推荐文章于 2023-11-17 11:53:58 发布

5ycode

最新推荐文章于 2023-11-17 11:53:58 发布

阅读量188

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/f80407515/article/details/78730500

版权

mysql 专栏收录该内容

17 篇文章 2 订阅

订阅专栏

CREATE TABLE `test` (
  `id` int(11) DEFAULT NULL,
  `count` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `test` (`id`, `count`) VALUES ('1', '6');
INSERT INTO `test` (`id`, `count`) VALUES ('2', '6');
INSERT INTO `test` (`id`, `count`) VALUES ('3', '8');
INSERT INTO `test` (`id`, `count`) VALUES ('4', '7');

# 动态拼接行转列语句
SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT('max(IF(t.id = ', c.id,',t.count,0)) AS ''',c.id,'''')
  )   INTO @sql
FROM test c;


SELECT @sql;

SET @sql = CONCAT('Select   ', @sql, 
                        ' from test  t 
                        ');

SELECT @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;