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) totalFROM    t_user tuLEFT JOIN t_kill tk ON tu.user_id = tk.user_idGROUP BY tu.name;
-- 使用 case whenSELECTsum(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) '沙僧'FROMt_user tuLEFT JOIN t_kill tk ON tu.user_id = tk.user_id;

3、行转列

-- 稍等

 

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) cntFROM    t_user tuLEFT 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) cntFROM    t_user tuLEFT JOIN t_kill tk ON tu.user_id = tk.user_id) c where cnt < 2ORDER BY name , number desc;

 

来源：https://www.icode9.com/content-2-801551.html