不管是面试还是真实业务场景都会经常遇到,行转列的场景。那么,在SQL语句开发中,我们怎样将一张表的数据通过行转列迁移到另外一张数据库表中,以保持数据的一致性。
比如我们需要将下面数据库表basedb.t_param中的数据,迁移到数据库表transferdb.t_new_param中。
id | key | value | status |
1 | ClassificationMode | 0 | 1 |
2 | size | 0,10,0 | 1 |
3 | statrValue | 15 | 1 |
4 | endValue | 100 | 1 |
5 | paramId | 101 | 1 |
id | classification_mode | size | start_value | end_value | param_id | status |
1 | 0 | 0,10,0 | 15 | 100 | 101 | 1 |
其中上面的建表语句如下
在MySQL中,有数据库basedb,数据库表t_param(迁移前参数表)
1.首先,创建数据迁移前的参数表basedb.t_param表,其SQL语句为:
DROP TABLE IF EXISTS `basedb`.`t_param`;
CREATE TABLE `basedb`.`t_param`(
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`key` varchar(15) DEFAULT NULL COMMENT 'key值',
`value` varchar(20) DEFAULT NULL COMMENT 'value值',
`status` tinyint(1) DEFAULT NULL COMMENT '状态',
PRIMARY KEY(`id`),
UNIQUE KEY `unique_index`(`key`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8 COLLATE = utf8_bin COMMENT = '迁移前参数表';
2. 其次创建迁移后新参数表basendb.t_new_param,其SQL语句为:
DROP TABLE IF EXISTS `basedb`.`t_new_param`;
CREATE TABLE `basedb`.`t_new_param`(
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`classification_mode` smallint(6) DEFAULT NULL COMMENT '分类模式',
`size` varchar(15) DEFAULT NULL COMMENT '格式',
`start_value` varchar(20) DEFAULT NULL COMMENT '起始值',
`end_value` varchar(20) DEFAULT NULL COMMENT '结束值',
`param_id` vsmallint(6) DEFAULT NULL COMMENT '参数ID',
`status` tinyint(1) DEFAULT NULL COMMENT '状态',
PRIMARY KEY(`id`),
UNIQUE KEY `unique_index`(`param_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8 COLLATE = utf8_bin COMMENT = '迁移后新参数表';
3. 以下我们在SQL语句中实现行转列的方法
INSERT INTO `basedb`.`t_new_param`(`classification_mode`,`size`,`start_value`,`end_value`,`param_id`,`status`)
SELECT
MAX(CASE 'key' WHEN 'ClassificationMode' THEN `value` END) as `classification_mode`,
MAX(CASE 'key' WHEN 'size' THEN `value` END) as `size`,
MAX(CASE 'key' WHEN 'statrValue' THEN `value` END) as `start_value`,
MAX(CASE 'key' WHEN 'endValue' THEN `value` END) as `end_value`,
MAX(CASE 'key' WHEN 'paramId' THEN `value` END) as `param_id`,
'1' as `status`
FROM `basedb`.`t_param` WHERE `key` IN (ClassificationMode,size,statrValue,endValue,paramId);
4. 总结
在我们做系统开发的时候,会使用SQL语句,其中避免不了做数据迁移,那么我们在做数据迁移以后,必须保持数据的一致性,这就需要我们使用SQL语句进行行转列。
在上面的行转列中,使用CASE... WHEN...THEN 获取相应字段的值,然后使用MAX聚合函数,用做行专列。
通用SQL:
INSERT INTO 新表名 (字段1,字段2) SELECT 字段1 as 字段1, 字段2 as 字段2 FROM 旧表名