MySQL开发技巧（二）_技术笔记

斜杠男人 2021-02-22

使用case方法实现行转列

截图
0赞 · 0采集
慕粉1100306574 2020-10-11

行转列sql

截图
0赞 · 0采集
慕哥6062902 2020-08-19

行列转换更好的方式

截图
0赞 · 0采集
慕后端2376581 2019-08-13

行转列语法：case

截图
0赞 · 0采集
我私发我 2019-06-15

case实现行列转换
select
sum(case when user_name='孙悟空' then kills end) as '孙悟空'
sum(case when user_name='bajie' then kills end) as 'bajie'
sum(case when user_name='shaseng' then kills end) as 'shaseng'
from user1 a join user_kills b on a.id=b.user_id

截图
0赞 · 0采集
精慕门6406361 2019-03-13

dsadas

0赞 · 0采集
精慕门6406361 2019-03-13

行转列 cast

0赞 · 0采集
12344554 2019-03-06

case when语句实现行转列

截图
0赞 · 0采集
慕工程4308039 2018-12-18

行转列：
CASE： case when 条件语句 then 返回值

0赞 · 0采集
慕沐8756185 2018-09-13

case when.....then方法行转列

截图
0赞 · 1采集
往生i 2018-09-04

mysql 使用 case when 实现行转列

截图
0赞 · 0采集
qq_柯九思_0 2018-08-14

select sum(case when user_name='孙悟空' then kills end) as ‘孙悟空’，sum(case when user_name=‘猪八戒’ then kills end) as ‘猪八戒’，sum(case when user_name=‘沙僧’) as '沙僧' from user1 a join user_kills b on a。id=b.user_id

0赞 · 0采集
慕神7056415 2018-06-25

1.分别查出数列的结果然后用cross jion 连接 2.用case when

截图
0赞 · 0采集

BirdOfV 2018-05-26

@MySQL---使用CASE语句实现行列转换

SELECT SUM(CASE WHEN user_name='孙悟空' THEN kills END) AS '孙悟空',
SUM(CASE WHEN user_name='猪八戒' THEN kills END) AS '猪八戒',
SUM(CASE WHEN user_name='沙僧' THEN kills END) AS '沙僧' 
FROM user1 a JOIN user_kills b ON a.id=b.user_id;

截图

0赞 · 1采集

论斤烤 2018-05-22

行转列
case when

截图
0赞 · 0采集
hwbaker 2018-01-26

执行结果中有 null select sum(case when a.user_id=1 then b.kills end) as 'wukong', sum(case when a.user_id=2 then b.kills end) as 'bajie', sum(case when a.user_id=3 then b.kills end) as 'shaseng' from user a join user_kills b on a.user_id = b.user_id group by b.user_id; 结果如下： +--------+-------+---------+ | wukong | bajie | shaseng | +--------+-------+---------+ | 50722 | NULL | NULL | | NULL | 15310 | NULL | | NULL | NULL | 2100 | +--------+-------+---------+ 3 rows in set (0.00 sec)

0赞 · 0采集
至高之拳 2017-08-05

case行转列

截图
0赞 · 0采集
碎碎的时光 2017-07-17

select sum(case when user_name='孙悟空' then kills end) as '孙悟空', sum(case when user_name='猪八戒',thwn kills end) as '猪八戒'） from user_name a join user_kills b on a.id = b.id

截图
2赞 · 0采集
滕玉龙 2017-06-13

2.使用case语句进行行列转换 select sum(case when user_name='孙悟空' then kills end) as '孙悟空', sum(case when user_name='猪八戒' then kills end) as '猪八戒', sum(case when user_name='沙僧' then kills end) as '沙僧' from user1 a join user_kills b on a.id=b.user_id; ----------------------------------------------- 使用CASE语句： SELECT SUM(CASE USER_NAME='A' THEN KILLS END) AS 'A', SUM(CASE USERNAME='B' THEN KILL END) AS 'B', SUM(CASE USERNAME='C' THEN KILL END) AS 'C' FROM A INNER JOIN B ON A.NAME=B.USERNAME; 通过CASE给满足WHEN语句的数据返回SUM聚合函数要统计的数据，再将SUM进行重命名。推荐。一般形式 SELECT NAME AS '姓名', SUM(CASE SUBJECT WHEN '数学' THEN MARK ELSE NULL END) AS '数学', SUM(CASE SUBJECT WHEN '英语' THEN MARK ELSE NULL END) AS '英语', SUM(CASE SUBJECT WHEN '语文' THEN MARK ELSE NULL END) AS '语文' FROM SUBJECT GROUP BY NAME; CASE语句中放原来表中要进行转换的字段（如学科），对其进行分类（如数学、英语、语文）。注意：要用聚合函数，最后要GROUP BY。

截图
1赞 · 2采集
qq_殊路同归_0 2017-05-15

case when then end 使用方式

截图
0赞 · 1采集
没死接着学 2017-02-13

使用CASE语句： SELECT SUM(CASE USER_NAME='A' THEN KILLS END) AS 'A', SUM(CASE USERNAME='B' THEN KILL END) AS 'B', SUM(CASE USERNAME='C' THEN KILL END) AS 'C' FROM A INNER JOIN B ON A.NAME=B.USERNAME; 通过CASE给满足WHEN语句的数据返回SUM聚合函数要统计的数据，再将SUM进行重命名。推荐。一般形式 SELECT NAME AS '姓名', SUM(CASE SUBJECT WHEN '数学' THEN MARK ELSE NULL END) AS '数学', SUM(CASE SUBJECT WHEN '英语' THEN MARK ELSE NULL END) AS '英语', SUM(CASE SUBJECT WHEN '语文' THEN MARK ELSE NULL END) AS '语文' FROM SUBJECT GROUP BY NAME; CASE语句中放原来表中要进行转换的字段（如学科），对其进行分类（如数学、英语、语文）。注意：要用聚合函数，最后要GROUP BY。

0赞 · 0采集
慕移动8064676 2016-10-22

行列转换技巧

截图
0赞 · 1采集
阳光微笑yiyi 2016-09-22

case

截图
0赞 · 0采集
canfeng918 2016-07-13

使用case语句实现行转列

截图
0赞 · 0采集
霜花似雪 2016-06-22

2.使用case语句进行行列转换 select sum(case when user_name='孙悟空' then kills end) as '孙悟空', sum(case when user_name='猪八戒' then kills end) as '猪八戒', sum(case when user_name='沙僧' then kills end) as '沙僧' from user1 a join user_kills b on a.id=b.user_id;

截图
0赞 · 1采集
java飙升 2016-06-15

case语句实现列转行 select sum(case when user_name=' 孙悟空' then kills end) as '孙悟空', sum(case when user_name='猪八戒' then kills end) as '猪八戒', sum(case when user_name='沙僧' then kills end) as '沙僧' from user1 a join use_kills b on a.id = b.user_id;

截图
0赞 · 1采集
mumukk 2016-06-06

行转列 case

截图
0赞 · 0采集
haoguangxun 2016-05-27

select sum(case when user_name=' 孙悟空' then kills end) as '孙悟空', sum(case when user_name='猪八戒' then kills end) as '猪八戒', sum(case when user_name='沙僧' then kills end) as '沙僧' from user1 a join use_kills b on a.id = b.user_id;

截图
0赞 · 0采集
纠结的小猴子 2016-05-19

使用cross join 方式是实现行列转换： 1.统计每一个用户打怪的总数 select sum(kills) as '沙僧' from user1 a left join user_kills b on a.id = b.user_id where a.username='沙僧'; select sum(kills) as '猪八戒' from user1 a left join user_kills b on a.id = b.user_id where a.username='猪八戒'; select sum(kills) as '孙悟空' from user1 a left join user_kills b on a.id = b.user_id where a.username='孙悟空'; 2.使用cross join 进行笛卡尔积连接，将三组数据进行行转列组合 select * from (select sum(kills) as '沙僧' from user1 a left join user_j=kills b on a.id = b.user_id and a.username='沙僧') a cross join (select sum(kills) as '猪八戒' from user1 a left join user_j=kills b on a.id = b.user_id and a.username='猪八戒') b cross join (select sum(kills) as '孙悟空' from user1 a left join user_j=kills b on a.id = b.user_id and a.username='孙悟空') c 总结：这种方法虽然可以转换行列，但是遇到需要组合的数据太多编写起来就比较繁琐，而且查询效率比较低！

0赞 · 1采集
纠结的小猴子 2016-05-19

2.使用case语句进行行列转换 select sum(case when user_name='孙悟空' then kills end) as '孙悟空', sum(case when user_name='猪八戒' then kills end) as '猪八戒', sum(case when user_name='沙僧' then kills end) as '沙僧' from user1 a join user_kills b on a.id=b.user_id

截图
0赞 · 0采集

数据加载中...