mysql 行转列，列转行

1、先创建基础表，添加数据，

CREATE TABLE `t_user` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户id',
  `name` varchar(255) DEFAULT NULL COMMENT '名称',
  `mobile` varchar(100) DEFAULT NULL COMMENT '电话',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

INSERT INTO `test`.`t_user` (`user_id`, `name`, `mobile`) VALUES ('1', '唐僧', '65651615,6111651,51651651,61565161,6156');
INSERT INTO `test`.`t_user` (`user_id`, `name`, `mobile`) VALUES ('2', '悟空', '452651651,478855,41223');
INSERT INTO `test`.`t_user` (`user_id`, `name`, `mobile`) VALUES ('3', '八戒', '325489,3214778,38955,39999');
INSERT INTO `test`.`t_user` (`user_id`, `name`, `mobile`) VALUES ('4', '沙僧', '25555,23333,21111');



CREATE TABLE `t_kill` (
`kill_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id主键',
`number` int(11) DEFAULT NULL COMMENT '杀敌数',
`time` datetime DEFAULT NULL COMMENT '时间',
`user_id` int(11) DEFAULT NULL COMMENT '用户id',
PRIMARY KEY (`kill_id`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;

 

INSERT INTO `test`.`t_kill` (`kill_id`, `number`, `time`, `user_id`) VALUES ('6', '12', '2020-12-27 15:31:23', '2');
INSERT INTO `test`.`t_kill` (`kill_id`, `number`, `time`, `user_id`) VALUES ('3', '5', '2020-12-26 15:31:23', '1');
INSERT INTO `test`.`t_kill` (`kill_id`, `number`, `time`, `user_id`) VALUES ('4', '8', '2020-12-25 15:31:23', '1');
INSERT INTO `test`.`t_kill` (`kill_id`, `number`, `time`, `user_id`) VALUES ('10', '15', '2020-12-24 15:31:23', '3');
INSERT INTO `test`.`t_kill` (`kill_id`, `number`, `time`, `user_id`) VALUES ('8', '10', '2020-12-18 15:31:23', '2');
INSERT INTO `test`.`t_kill` (`kill_id`, `number`, `time`, `user_id`) VALUES ('7', '52', '2020-12-08 15:31:23', '2');
INSERT INTO `test`.`t_kill` (`kill_id`, `number`, `time`, `user_id`) VALUES ('5', '47', '2020-11-28 15:31:23', '2');
INSERT INTO `test`.`t_kill` (`kill_id`, `number`, `time`, `user_id`) VALUES ('9', '8', '2020-10-27 15:31:23', '3');
INSERT INTO `test`.`t_kill` (`kill_id`, `number`, `time`, `user_id`) VALUES ('1', '15', '2020-10-18 15:31:23', '1');
INSERT INTO `test`.`t_kill` (`kill_id`, `number`, `time`, `user_id`) VALUES ('15', '11', '2020-10-17 15:31:23', '4');
INSERT INTO `test`.`t_kill` (`kill_id`, `number`, `time`, `user_id`) VALUES ('14', '12', '2020-10-16 15:31:23', '4');
INSERT INTO `test`.`t_kill` (`kill_id`, `number`, `time`, `user_id`) VALUES ('13', '21', '2020-10-15 15:31:23', '4');
INSERT INTO `test`.`t_kill` (`kill_id`, `number`, `time`, `user_id`) VALUES ('12', '35', '2020-10-11 15:31:23', '3');
INSERT INTO `test`.`t_kill` (`kill_id`, `number`, `time`, `user_id`) VALUES ('16', '9', '2020-10-10 15:31:23', '4');
INSERT INTO `test`.`t_kill` (`kill_id`, `number`, `time`, `user_id`) VALUES ('2', '9', '2020-09-26 15:31:23', '1');
INSERT INTO `test`.`t_kill` (`kill_id`, `number`, `time`, `user_id`) VALUES ('11', '24', '2020-09-25 15:31:23', '3');

2.列转行， 使用 case when

-- 查询用户杀敌数 
SELECT
    tu.user_id,
    tu.`name`,
    SUM(tk.number) total
FROM
    t_user tu
LEFT JOIN t_kill tk ON tu.user_id = tk.user_id
GROUP BY tu.name;

-- 使用 case when
SELECT
sum(CASE WHEN tu.name = '唐僧' then tk.number end) '唐僧',
sum(CASE WHEN tu.name = '悟空' then tk.number end) '悟空',
sum(CASE WHEN tu.name = '八戒' then tk.number end) '八戒',
sum(CASE WHEN tu.name = '沙僧' then tk.number end) '沙僧'
FROM
t_user tu
LEFT JOIN t_kill tk ON tu.user_id = tk.user_id
;

3、行转列

3.1 单行转列：

-- 生成一张序列表
CREATE TABLE `t_sequence` (
  `id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- 行转列，可替换t_user表名和字段使用
SELECT
    NAME,
    REPLACE (
        SUBSTRING(
            SUBSTRING_INDEX(mobile, ',', a.id),
            CHAR_LENGTH(
                SUBSTRING_INDEX(mobile, ',', a.id - 1)
            ) + 1
        ),
        ',',
        ''
    ) AS moblie
FROM
    t_sequence a
CROSS JOIN (
    SELECT
        NAME,
        CONCAT(mobile, ',') AS mobile,
        LENGTH(mobile) - LENGTH(REPLACE(mobile, ',', '')) + 1 size
    FROM
        t_user b
) b ON a.id <= b.size;

 3.1 多行转列：

-- 基础数据
SELECT name,arms,clothing,shoe from t_user tu LEFT JOIN t_equipment te on tu.user_id = te.user_id

-- 使用union all
SELECT name,'arms' equipment,arms from t_user tu LEFT JOIN t_equipment te on tu.user_id = te.user_id
union all
SELECT name,'clothing' equipment,clothing from t_user tu LEFT JOIN t_equipment te on tu.user_id = te.user_id
union all
SELECT name,'shoe' equipment,shoe from t_user tu LEFT JOIN t_equipment te on tu.user_id = te.user_id

-- 使用序列表
-- 一行变三行
SELECT name,arms,clothing,shoe from t_user tu 
LEFT JOIN t_equipment te on tu.user_id = te.user_id
CROSS join t_sequence ts where ts.id<4 ORDER BY name

使用 case when

SELECT name,
case when ts.id = 1 then 'arms' end,
case when ts.id = 2 then 'clothing' end,
case when ts.id = 3 then 'shoe' end as equipment,
case when ts.id = 1 then arms end,
case when ts.id = 2 then clothing end,
case when ts.id = 3 then shoe end
from t_user tu
LEFT JOIN t_equipment te on tu.user_id = te.user_id
CROSS join t_sequence ts where ts.id<4
ORDER BY name

-- COALESCE 只取非控值
SELECT name,
COALESCE(case when ts.id = 1 then 'arms' end,
                    case when ts.id = 2 then 'clothing' end,
                    case when ts.id = 3 then 'shoe' end) as equipment,
COALESCE(case when ts.id = 1 then arms end,
                    case when ts.id = 2 then clothing end,
                    case when ts.id = 3 then shoe end) as eq_name
from t_user tu 
LEFT JOIN t_equipment te on tu.user_id = te.user_id
CROSS join t_sequence ts where ts.id<4 
ORDER BY name;

 cross join 学习可观看：https://www.cnblogs.com/mzq123/p/11783698.html

4、分组查询前几条数据

-- 查询每一条数据，cnt是t_kill表中number比当前数据number大的数据的条数
SELECT
    tu.user_id,
    tu.`name`,
    tk.kill_id,
    tk.number,
    tk.time,
    ( SELECT count(1) from t_kill t where t.number > tk.number and t.user_id = tu.user_id) cnt
FROM
    t_user tu
LEFT JOIN t_kill tk ON tu.user_id = tk.user_id;

-- 根据cnt查询数据
SELECT * from (SELECT
    tu.user_id,
    tu.`name`,
    tk.kill_id,
    tk.number,
    tk.time,
    ( SELECT count(1) from t_kill t where t.number > tk.number and t.user_id = tu.user_id) cnt
FROM
    t_user tu
LEFT JOIN t_kill tk ON tu.user_id = tk.user_id) c 
where cnt < 2
ORDER BY name , number desc;