• 2022-03-10 17:20:32

首先有两个初始数据表分别为 collection_bill 和 collection_pool 。
collection_bill

idamountcollection_pool_id
cb000110cp0001
cb000210cp0002
cb000310cp0003
cb000410cp0001
cb000510cp0002
cb000610cp0002

collection_pool

idbiz_codepayee_idpayer_id
cp0001110012001
cp0002210012001
cp0003310012001

summary

payee_idpayer_idtotal_amount_code1total_amount_code2total_amount_code3
10012001203010

通过以下的 SQL 可以将 collection_bill 和 collection_pool 得到 summary 的结果。

with tmp as(
select
collection_pool_id,
sum(amount) total_amount
from collection_bill
group by collection_pool_id
)
select
payee_id,
payer_id,
sum(case when biz_code=1 then total_amount else 0 end) total_amount_code1,
sum(case when biz_code=2 then total_amount else 0 end) total_amount_code2,
sum(case when biz_code=3 then total_amount else 0 end) total_amount_code3
from
collection_pool a,
tmp b
where a.id = b.collection_pool_id
group by payee_id,payer_id


为什么这么做

1. 在相同的交易双方的前提下，因为不同的生意类型，会出现多个费用池（ collection_pool ）。
2. 不同的费用池会产生多条账单（ collection_bill ）。
3. 如果要统计交易双发不同生意类型的交易总额即可通过以上 SQL 实现。

关键
group by payee_id,payer_id 进行分组后，通过 sum(case when biz_code=1 then total_amount else 0 end) total_amount_code1sum 之前用 case when then end 筛选数据。

更多相关内容
• ## mysqL实现行转列

千次阅读 2022-01-04 17:17:27
原始表 转换后效果 转换语句 SELECT code , name, SUM(CASE mon WHEN '01' THEN up ELSE 0 END) as 'm1', SUM(CASE mon WHEN '02' THEN up ELSE 0 END) as 'm2', SUM(CASE mon WHEN '03' THEN up ELSE ...

原始表

转换后效果

转换语句

SELECT code ,  name,
SUM(CASE mon WHEN '01' THEN up ELSE 0 END) as 'm1',
SUM(CASE mon WHEN '02' THEN up ELSE 0 END) as 'm2',
SUM(CASE mon WHEN '03' THEN up ELSE 0 END) as 'm3',
SUM(CASE mon WHEN '04' THEN up ELSE 0 END) as 'm4',
SUM(CASE mon WHEN '05' THEN up ELSE 0 END) as 'm5',
SUM(CASE mon WHEN '06' THEN up ELSE 0 END) as 'm6',
SUM(CASE mon WHEN '07' THEN up ELSE 0 END) as 'm7',
SUM(CASE mon WHEN '08' THEN up ELSE 0 END) as 'm8',
SUM(CASE mon WHEN '09' THEN up ELSE 0 END) as 'm9',
SUM(CASE mon WHEN '10' THEN up ELSE 0 END) as 'm10',
SUM(CASE mon WHEN '11' THEN up ELSE 0 END) as 'm11',
SUM(CASE mon WHEN '12' THEN up ELSE 0 END) as 'm12',
FROM fund_range_mon where year='2022'
GROUP BY code

展开全文
• 姓名 语文 数学 英语 张三 78 88 98 李四 89 76 90 王五 89 56 89 解题 ​ 这是一道典型的行转列题目，给定表中行有学科，而目标表中是学科。 解法一：case when ​ 通过case when，可以较容易的实现将行转换成...

## 题目

​ 给定 student_score 表，内容如下：

select * from student_score;

nane  subject  score
-------------------------------
张三	  语文	78
张三	  数学	88
张三	  英语	98
李四	  语文	89
李四	  数学	76
李四	  英语	90
王五	  语文	99
王五	  数学	66
王五	  英语	91


​ 要求用一条 sql 语句查出如下结果：

姓名   语文  数学  英语
张三    78    88    98
李四    89    76    90
王五    89    56    89


## 解题

​ 这是一道典型的行转列题目，给定表中行有学科，而目标表中列是学科。

### 解法一：case when

​ 通过case when，可以较容易的实现将行转换成列的功能，这是通用的思路。

SELECT NAME,
max( CASE SUBJECT WHEN '语文' THEN score END ) '语文',
max( CASE SUBJECT WHEN '数学' THEN score END ) '数学',
max( CASE SUBJECT WHEN '英语' THEN score END ) '英语'
FROM
student_score
GROUP BY
NAME


### 解法二：join

​ 解法二和解法三在部分情况下是可行的，用来拓展思路。

--1. 先查询出每个学科表数据，即获取一列的数据
select name,score 'y' from student_score where subject = '语文';
select name,score 's' from student_score where subject = '数学';
select name,score 'e' from student_score where subject = '英语';
/*
查询结果类似如下：
name  y
张三	78
李四	89
王五	99
*/

--2. 将第1步查出的三张表进行连接，得出最终结果
SELECT
ss1.NAME,
ss1.y '语文',
ss2.s '数学',
ss3.e '英语'
FROM
( SELECT NAME, score 'y' FROM student_score WHERE SUBJECT = '语文' ) ss1
JOIN ( SELECT NAME, score 's' FROM student_score WHERE SUBJECT = '数学' ) ss2 ON ss1.NAME = ss2.NAME
JOIN ( SELECT NAME, score 'e' FROM student_score WHERE SUBJECT = '英语' ) ss3 ON ss1.NAME = ss3.NAME;


### 解法三：union all

--1. 第1步和 join 方式一样，查询出每个学科表数据
select name,score 'y' from student_score where subject = '语文';
select name,score 's' from student_score where subject = '数学';
select name,score 'e' from student_score where subject = '英语';
/*
查询结果类似如下：
name  y
张三	78
李四	89
王五	99
*/

--2. 通过 union all 将三个表合并起来，并通过 name 进行分组，合并成最终结果
SELECT
s.NAME,
max( s.y ) '语文',
max( s.s ) '数学',
max( s.e ) '英语'
FROM
(	SELECT NAME, score y, 0 s, 0 e FROM	student_score WHERE	SUBJECT = '语文'
UNION ALL
select name,0 y,score s, 0 e from student_score where subject = '数学'
UNION ALL
select name,0 y, 0 s, score e from student_score where subject = '英语' ) s
GROUP BY s.name ;

展开全文
• ## MySQL实现行转列SQL

万次阅读 多人点赞 2018-06-03 21:52:36
概述好久没写SQL语句，今天看到问答中的一个问题，拿来研究一下。问题链接：关于Mysql 的分级输出问题情景简介学校里面记录成绩，每个人的...数据库表数据：处理后的结果（行转列）：方法一：这里可以使用Max，也可...

# 概述

好久没写SQL语句，今天看到问答中的一个问题，拿来研究一下。

问题链接：关于Mysql 的分级输出问题

# 情景简介

学校里面记录成绩，每个人的选课不一样,而且以后会添加课程，所以不需要把所有课程当作列。数据表里面数据如下图，使用姓名+课程作为联合主键（有些需求可能不需要联合主键）。本文以MySQL为基础，其他数据库会有些许语法不同。

数据库表数据：

# 处理后的结果（行转列）：

方法一：

这里可以使用Max，也可以使用Sum；

注意第二张图，当有学生的某科成绩缺失的时候，输出结果为Null;

[sql]  view plain  copy
1. SELECT
2.     SNAME,
3.     MAX(
4.         CASE CNAME
5.         WHEN 'JAVA' THEN
6.             SCORE
7.         END
8.     ) JAVA,
9.     MAX(
10.         CASE CNAME
11.         WHEN 'mysql' THEN
12.             SCORE
13.         END
14.     ) mysql
15. FROM
16.     stdscore
17. GROUP BY
18.     SNAME;

可以在第一个Case中加入Else语句解决这个问题:

[sql]  view plain  copy
1. SELECT
2.     SNAME,
3.     MAX(
4.         CASE CNAME
5.         WHEN 'JAVA' THEN
6.             SCORE
7.         ELSE
8.             0
9.         END
10.     ) JAVA,
11.     MAX(
12.         CASE CNAME
13.         WHEN 'mysql' THEN
14.             SCORE
15.         ELSE
16.             0
17.         END
18.     ) mysql
19. FROM
20.     stdscore
21. GROUP BY
22.     SNAME;
方法二：

[sql]  view plain  copy
1. SELECT DISTINCT  a.sname,
2. (SELECT score FROM stdscore b WHERE a.sname=b.sname AND b.CNAME='JAVA' ) AS 'JAVA',
3. (SELECT score FROM stdscore b WHERE a.sname=b.sname AND b.CNAME='mysql' ) AS 'mysql'
4. FROM stdscore a

方法三：

[sql]  view plain  copy
1. DROP PROCEDURE
2. IF EXISTS sp_score;
3. DELIMITER &&
4.
5. CREATE PROCEDURE sp_score ()
6. BEGIN
7.     #课程名称
8.     DECLARE
9.         cname_n VARCHAR (20) ; #所有课程数量
10.         DECLARE
11.             count INT ; #计数器
12.             DECLARE
13.                 i INT DEFAULT 0 ; #拼接SQL字符串
14.             SET @s = 'SELECT sname' ;
15.             SET count = (
16.                 SELECT
17.                     COUNT(DISTINCT cname)
18.                 FROM
19.                     stdscore
20.             ) ;
21.             WHILE i < count DO
22.
23.
24.             SET cname_n = (
25.                 SELECT
26.                     cname
27.                 FROM
28.                     stdscore
29.                 GROUP BY CNAME
30.                 LIMIT i,
31.                 1
32.             ) ;
33.             SET @s = CONCAT(
34.                 @s,
35.                 ', SUM(CASE cname WHEN ',
36.                 '\'',
37.                 cname_n,
38.                 '\'',
39.                 ' THEN score ELSE 0 END)',
40.                 ' AS ',
41.                 '\'',
42.                 cname_n,
43.                 '\''
44.             ) ;
45.             SET i = i + 1 ;
46.             END
47.             WHILE ;
48.             SET @s = CONCAT(
49.                 @s,
50.                 ' FROM stdscore GROUP BY sname'
51.             ) ; #用于调试
52.             #SELECT @s;
53.             PREPARE stmt
54.             FROM
55.                 @s ; EXECUTE stmt ;
56.             END&&
57.
58. CALL sp_score () ;

# 处理后的结果（行转列）分级输出：

方法一：

这里可以使用Max，也可以使用Sum；

注意第二张图，当有学生的某科成绩缺失的时候，输出结果为Null;

[sql]  view plain  copy
1. SELECT
2.     SNAME,
3.     MAX(
4.         CASE CNAME
5.         WHEN 'JAVA' THEN
6.             (
7.                 CASE
8.                 WHEN SCORE - (select avg(SCORE) from stdscore where CNAME='JAVA') > 20 THEN
9.                     '优秀'
10.                 WHEN SCORE - (select avg(SCORE) from stdscore where CNAME='JAVA') > 10 THEN
11.                     '良好'
12.                 WHEN SCORE - (select avg(SCORE) from stdscore where CNAME='JAVA') >= 0 THEN
13.                     '普通'
14.                 ELSE
15.                     '较差'
16.                 END
17.             )
18.         END
19.     ) JAVA,
20.     MAX(
21.         CASE CNAME
22.         WHEN 'mysql' THEN
23.             (
24.                 CASE
25.                 WHEN SCORE - (select avg(SCORE) from stdscore where CNAME='JAVA') > 20 THEN
26.                     '优秀'
27.                 WHEN SCORE - (select avg(SCORE) from stdscore where CNAME='JAVA') > 10 THEN
28.                     '良好'
29.                 WHEN SCORE - (select avg(SCORE) from stdscore where CNAME='JAVA') >= 0 THEN
30.                     '普通'
31.                 ELSE
32.                     '较差'
33.                 END
34.             )
35.         END
36.     ) mysql
37. FROM
38.     stdscore
39. GROUP BY
40.     SNAME;

方法二：

[sql]  view plain  copy
1. SELECT DISTINCT  a.sname,
2. (SELECT (
3.                 CASE
4.                 WHEN SCORE - (select avg(SCORE) from stdscore where CNAME='JAVA') > 20 THEN
5.                     '优秀'
6.                 WHEN SCORE - (select avg(SCORE) from stdscore where CNAME='JAVA') > 10 THEN
7.                     '良好'
8.                 WHEN SCORE - (select avg(SCORE) from stdscore where CNAME='JAVA') >= 0 THEN
9.                     '普通'
10.                 ELSE
11.                     '较差'
12.                 END
13.             ) FROM stdscore b WHERE a.sname=b.sname AND b.CNAME='JAVA' ) AS 'JAVA',
14. (SELECT (
15.                 CASE
16.                 WHEN SCORE - (select avg(SCORE) from stdscore where CNAME='JAVA') > 20 THEN
17.                     '优秀'
18.                 WHEN SCORE - (select avg(SCORE) from stdscore where CNAME='JAVA') > 10 THEN
19.                     '良好'
20.                 WHEN SCORE - (select avg(SCORE) from stdscore where CNAME='JAVA') >= 0 THEN
21.                     '普通'
22.                 ELSE
23.                     '较差'
24.                 END
25.             ) FROM stdscore b WHERE a.sname=b.sname AND b.CNAME='mysql' ) AS 'mysql'
26. FROM stdscore a

方法三：

[sql]  view plain  copy
1. DROP PROCEDURE
2. IF EXISTS sp_score;
3. DELIMITER &&
4.
5. CREATE PROCEDURE sp_score ()
6. BEGIN
7.     #课程名称
8.     DECLARE
9.         cname_n VARCHAR (20) ; #所有课程数量
10.         DECLARE
11.             count INT ; #计数器
12.             DECLARE
13.                 i INT DEFAULT 0 ; #拼接SQL字符串
14.             SET @s = 'SELECT sname' ;
15.             SET count = (
16.                 SELECT
17.                     COUNT(DISTINCT cname)
18.                 FROM
19.                     stdscore
20.             ) ;
21.             WHILE i < count DO
22.
23.
24.             SET cname_n = (
25.                 SELECT
26.                     cname
27.                 FROM
28.                     stdscore
29.         GROUP BY CNAME
30.                 LIMIT i, 1
31.             ) ;
32.             SET @s = CONCAT(
33.                 @s,
34.                 ', MAX(CASE cname WHEN ',
35.                 '\'',
36.                 cname_n,
37.                 '\'',
38.                 ' THEN (
39.                 CASE
40.                 WHEN SCORE - (select avg(SCORE) from stdscore where CNAME=\'',cname_n,'\') > 20 THEN
41.                     \'优秀\'
42.                 WHEN SCORE - (select avg(SCORE) from stdscore where CNAME=\'',cname_n,'\') > 10 THEN
43.                     \'良好\'
44.                 WHEN SCORE - (select avg(SCORE) from stdscore where CNAME=\'',cname_n,'\') >= 0 THEN
45.                     \'普通\'
46.                 ELSE
47.                     \'较差\'
48.                 END
49.             ) END)',
50.                 ' AS ',
51.                 '\'',
52.                 cname_n,
53.                 '\''
54.             ) ;
55.             SET i = i + 1 ;
56.             END
57.             WHILE ;
58.             SET @s = CONCAT(
59.                 @s,
60.                 ' FROM stdscore GROUP BY sname'
61.             ) ;
62.             #用于调试
63.             #SELECT @s;
64.             PREPARE stmt
65.             FROM
66.                 @s ; EXECUTE stmt ;
67.             END&&
68.
69.
70. CALL sp_score ();

# 几种方法比较分析

第一种使用了分组，对每个课程分别处理。
第二种方法使用了表连接。
第三种使用了存储过程，实际上可以是第一种或第二种方法的动态化，先计算出所有课程的数量，然后对每个分组进行课程查询。 这种方法的一个最大的好处是当新增了一门课程时，SQL语句不需要重写。

# 小结

关于行转列和列转行

这个概念似乎容易弄混，有人把行转列理解为列转行，有人把列转行理解为行转列；

这里做个定义：

行转列：把表中特定列（如本文中的：CNAME）的数据去重后做为列名（如查询结果行中的“Java，mysql”，处理后是做为列名输出）；

列转行：可以说是行转列的反转，把表中特定列（如本文处理结果中的列名“JAVA，mysql”）做为每一行数据对应列“CNAME”的值；

关于效率

不知道有什么好的生成模拟数据的方法或工具，麻烦小伙伴推荐一下，抽空我做一下对比；

还有其它更好的方法吗？

本文使用的几种方法应该都有优化的空间，特别是使用存储过程的话会更加灵活，功能更强大；

本文的分级只是给出一种思路，分级的方法如果学生的成绩相差较小的话将失去意义；

如果小伙伴有更好的方法，还请不吝赐教，感激不尽！

有些需求可能不需要联合主键

有些需求可能不需要联合主键，因为一门课程可能允许学生考多次，取最好的一次成绩，或者取多次的平均成绩。

原文地址： http://blog.csdn.net/testcs_dn/article/details/49847299
展开全文
• mysql动态行转列的例子
• 即一中存储了多个属性值。如下表 pk value 1 ET,AT 2 AT,BT 3 AT,DT 4 DT,CT,AT 一般有这两种常见需求(测试数据见文末) 1.得到所有的不重复的值，如 value AT BT CT DT ET SQL...
• 主要介绍了mysql 行转列转行实例详解的相关资料,需要的朋友可以参考下
• ## MySQL---行转列

千次阅读 2021-03-02 13:52:34
MySQL—行转列 一、行转列 1、SqlServer和Orcle中可以使用pivot行转列函数快速实现，而MySQL中没有 ...2、MySQL实现行转列 SELECT name, MAX( CASE WHEN subject='语文' THEN score ELSE 0 END) AS "语文", MAX
• 最近在开发过程中遇到问题，需要将数据库中一张表信息进行行转列操作，再将每(即每个字段)作为与其他表进行联表查询的字段进行显示。借此机会，在网上查阅了相关方法，现总结出一种比较简单易懂的方法备用。一、...
• ## MySQL 动态 行转列

千次阅读 2022-02-24 16:54:08
MySQL 动态 行转列1.需求2.建表3.插入数据4. 转换前结果5. 动态转换 1.需求 在每一行的内容不确定的情况下，需要动态的把行转为。 2.建表 DROP TABLE IF EXISTS tb_score; CREATE TABLE tb_score( id INT(11) ...
• ## MySql行转列&列转行

万次阅读 2022-01-09 15:26:38
行转列： 创建语句： create table test1( id int auto_increment primary key , name varchar(255), course varchar(255), score int ) insert into test1(name,course,score) values ('张三','语文',120); insert...
• 顺道也总结一下行转列   话不多说，现在开始 ~~       行转列：   转换之前的表格，第三、四分别为特征和数值 图1     首先看第一次的执行sql： select id , name , (case ...
• ## MySQL行转列函数

万次阅读 2019-11-04 09:07:51
原文链接： ...概述 好久没写SQL语句，今天看到问答中的一个问题，拿来...学校里面记录成绩，每个人的选课不一样,而且以后会添加课程，所以不需要把所有课程当作。数据表里面数据如下图，使用姓名+课程作为联合主键（...
• 需求背景：在任务管理系统中，有任务详情表，每个任务下... 因为每个任务的时间范围不一样，所以需要用到动态的行转列，将时间周期作为动态的，进度做为的值。 任务节点表： quest_node_id：任务子节点id，...
• MYSQL列转行的两种做法
• ## MySQL实现pivot行转列

万次阅读 2019-04-11 00:28:43
3.实现行转列方式一：   SELECT name,  SUM(salary1) '2016',  SUM(salary2) '2017',  SUM(salary3) '2018',  SUM(salary4) '2019' FROM (  SELECT name,     CASE WHEN year= '2016' ...
• 一篇文章搞定mysql行转列（7种方法） 和 转行
• 行转列一般通过CASE WHEN 语句来实现，也可以通过 SQL SERVER 的运算符PIVOT来实现。用传统的方法，比较好理解。层次清晰，而且比较习惯。 但是PIVOT 、UNPIVOT提供的语法比一系列复杂的SELECT…CASE 语句中所指定的...
• } 下面是执行计划，其他测试暂时没做，空了看看这种sql效率会如何 以上就是mysql行转列实现。 下面是经历 客户有一个需求，页面数据是动态或者动态属性（相当于没有固定的entity），同一模型下不同类型对应的...
• 在日常工作中，或者面试过程中，常常会碰到要求用SQL语句实现行转列。形式如下： select * from test; 而面试官要求查询结果如下展示： 或者这样： 其实很简单~我们可以使用case when语句进行行转列...
• ## Mysql实现行列转换

千次阅读 2022-02-15 15:41:04
mysql数据库如何实现行列转换 方案一： select name, sum(case when course='java' then grade end) as java, sum(case when course='C++' then grade end) as C++, sum(case when course='C#' then grade end) as...
• 使用union 来实现列转行 这里我偷懒了，将行转列的结果作为视图，没有重新建表，效果是一样的。 SELECT * FROM ( SELECT year , 1 AS 'month' , m1 AS amount FROM ffe UNION ...
• 两个方式，用任何一个都可以实现。 1.用cross join的方式实现 select * from (select sum(a.kills) '孙悟空' from kills1 as a LEFT JOIN tssrz as b on a.user_id = b.id WHERE b.user_name = '孙悟空' ...
• 行列转换常见场景 由于很多业务表因为历史原因或者性能原因，都使用了违反第一范式的设计模式。...id构建：如有一数据value有100个逗号分割的值，需要循环100次，mysql内部也有属性表help_topic自增id help.
• 从网上找了很多行转列的。基本都是2行的行转列。不带日期分组的。 借鉴了另一个哥们的文章，实现了自己想要的结果，写出来大家可以参考。以后自己遇到同样情况，也可以有个备份 借鉴的地址为 ...
• hive 行转列/转行 多行转一行/一行转多行 Mysql 创建表语句: CREATE TABLE student_score( id BIGINT PRIMARY key auto_increment, s_name VARCHAR(20) , s_sub VARCHAR(20), s_score INT ); insert into student_...
• 这样的语句来实现行转列 但我们都知道，课程不仅仅这几门，如果用上面的语句去写，第一要确定有多少课程，这么多课程的课程名要再拿出来，那样的话写一个查询语句下来，可是要写很多了。那么就想能不能动态进行行转...
• 即一中存储了多个属性值。如下表 pk value 1 ET,AT 2 AT,BT 3 AT,DT 4 DT,CT,AT 一般有这两种常见需求： 得到所有的不重复的值，如 value ...

...

mysql 订阅