mysql行列转换方法总结

最新推荐文章于 2022-12-13 08:00:00 发布
最新推荐文章于 2022-12-13 08:00:00 发布
阅读量565 收藏
点赞数


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
create table tx(
  id int primary key ,
  c1 char (2),
  c2 char (2),
  c3 int
);
insert into tx values
(1 , 'A1' , 'B1' ,9),
(2 , 'A2' , 'B1' ,7),
(3 , 'A3' , 'B1' ,4),
(4 , 'A4' , 'B1' ,2),
(5 , 'A1' , 'B2' ,2),
(6 , 'A2' , 'B2' ,9),
(7 , 'A3' , 'B2' ,8),
(8 , 'A4' , 'B2' ,5),
(9 , 'A1' , 'B3' ,1),
(10 , 'A2' , 'B3' ,8),
(11 , 'A3' , 'B3' ,8),
(12 , 'A4' , 'B3' ,6),
(13 , 'A1' , 'B4' ,8),
(14 , 'A2' , 'B4' ,2),
(15 , 'A3' , 'B4' ,6),
(16 , 'A4' , 'B4' ,9),
(17 , 'A1' , 'B4' ,3),
(18 , 'A2' , 'B4' ,5),
(19 , 'A3' , 'B4' ,2),
(20 , 'A4' , 'B4' ,5);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
mysql> select * from tx;
+----+------+------+------+
| id | c1   | c2   | c3   |
+----+------+------+------+
|  1 | A1   | B1   |    9 |
|  2 | A2   | B1   |    7 |
|  3 | A3   | B1   |    4 |
|  4 | A4   | B1   |    2 |
|  5 | A1   | B2   |    2 |
|  6 | A2   | B2   |    9 |
|  7 | A3   | B2   |    8 |
|  8 | A4   | B2   |    5 |
|  9 | A1   | B3   |    1 |
| 10 | A2   | B3   |    8 |
| 11 | A3   | B3   |    8 |
| 12 | A4   | B3   |    6 |
| 13 | A1   | B4   |    8 |
| 14 | A2   | B4   |    2 |
| 15 | A3   | B4   |    6 |
| 16 | A4   | B4   |    9 |
| 17 | A1   | B4   |    3 |
| 18 | A2   | B4   |    5 |
| 19 | A3   | B4   |    2 |
| 20 | A4   | B4   |    5 |
+----+------+------+------+
20 rows in set (0.00 sec)
mysql>

期望结果

1
2
3
4
5
6
7
8
9
+------+-----+-----+-----+-----+------+
|C1    |B1   |B2   |B3   |B4   |Total |
+------+-----+-----+-----+-----+------+
|A1    |9    |2    |1    |11   |23    |
|A2    |7    |9    |8    |7    |31    |
|A3    |4    |8    |8    |8    |28    |
|A4    |2    |5    |6    |14   |27    |
|Total |22   |24   |23   |40   |109   |
+------+-----+-----+-----+-----+------+

1. 利用SUM(IF()) 生成列 + WITH ROLLUP 生成汇总行,并利用 IFNULL将汇总行标题显示为 Total

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
mysql>
SELECT
IFNULL(c1, 'total' ) AS total,
SUM(IF(c2= 'B1' ,c3,0)) AS B1,
SUM(IF(c2= 'B2' ,c3,0)) AS B2,
SUM(IF(c2= 'B3' ,c3,0)) AS B3,
SUM(IF(c2= 'B4' ,c3,0)) AS B4,
SUM(IF(c2= 'total' ,c3,0)) AS total
FROM (
SELECT c1,IFNULL(c2, 'total' ) AS c2,SUM(c3) AS c3
FROM tx   GROUP BY c1,c2
WITH ROLLUP
HAVING c1 IS NOT NULL
) AS A
GROUP BY c1
WITH ROLLUP;
 
+-------+------+------+------+------+-------+
| total | B1   | B2   | B3   | B4   | total |
+-------+------+------+------+------+-------+
| A1    |    9 |    2 |    1 |   11 |    23 |
| A2    |    7 |    9 |    8 |    7 |    31 |
| A3    |    4 |    8 |    8 |    8 |    28 |
| A4    |    2 |    5 |    6 |   14 |    27 |
| total |   22 |   24 |   23 |   40 |   109 |
+-------+------+------+------+------+-------+
5 rows in set , 1 warning (0.00 sec)

2. 利用SUM(IF()) 生成列 + UNION 生成汇总行,并利用 IFNULL将汇总行标题显示为 Total

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
mysql>
select c1,
sum ( if (c2= 'B1' ,C3,0)) AS B1,
sum ( if (c2= 'B2' ,C3,0)) AS B2,
sum ( if (c2= 'B3' ,C3,0)) AS B3,
sum ( if (c2= 'B4' ,C3,0)) AS B4,SUM(C3) AS TOTAL
from tx
group by C1
UNION
SELECT 'TOTAL' , sum ( if (c2= 'B1' ,C3,0)) AS B1,
sum ( if (c2= 'B2' ,C3,0)) AS B2,
sum ( if (c2= 'B3' ,C3,0)) AS B3,
sum ( if (c2= 'B4' ,C3,0)) AS B4,SUM(C3) FROM TX;
 
+-------+------+------+------+------+-------+
| c1    | B1   | B2   | B3   | B4   | TOTAL |
+-------+------+------+------+------+-------+
| A1    |    9 |    2 |    1 |   11 |    23 |
| A2    |    7 |    9 |    8 |    7 |    31 |
| A3    |    4 |    8 |    8 |    8 |    28 |
| A4    |    2 |    5 |    6 |   14 |    27 |
| TOTAL |   22 |   24 |   23 |   40 |   109 |
+-------+------+------+------+------+-------+
5 rows in set (0.00 sec)
mysql>

3.  利用SUM(IF()) 生成列,直接生成结果不再利用子查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mysql>
select
ifnull(c1, 'total' ),
sum ( if (c2= 'B1' ,C3,0)) AS B1,
sum ( if (c2= 'B2' ,C3,0)) AS B2,
sum ( if (c2= 'B3' ,C3,0)) AS B3,
sum ( if (c2= 'B4' ,C3,0)) AS B4,SUM(C3) AS TOTAL
from tx
group by C1 with rollup ;
 
+--------------------+------+------+------+------+-------+
| ifnull(c1, 'total' ) | B1   | B2   | B3   | B4   | TOTAL |
+--------------------+------+------+------+------+-------+
| A1                 |    9 |    2 |    1 |   11 |    23 |
| A2                 |    7 |    9 |    8 |    7 |    31 |
| A3                 |    4 |    8 |    8 |    8 |    28 |
| A4                 |    2 |    5 |    6 |   14 |    27 |
| total              |   22 |   24 |   23 |   40 |   109 |
+--------------------+------+------+------+------+-------+
5 rows in set (0.00 sec)
mysql>

4. 动态，适用于列不确定情况，

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mysql> SET @EE= '' ;
mysql> SELECT @EE:=CONCAT(@EE, 'SUM(IF(C2=\'' ,C2, '\'' , ',C3,0)) AS ' ,C2, ',' ) FROM (SELECT DISTINCT C2 FROM TX) A;
 
mysql> SET @QQ=CONCAT( 'SELECT ifnull(c1,\'total\'),' ,LEFT(@EE,LENGTH(@EE)-1), ' ,SUM(C3) AS TOTAL FROM TX GROUP BY C1 WITH ROLLUP' );
Query OK, 0 rows affected (0.00 sec)
 
mysql> PREPARE stmt2 FROM @QQ;
Query OK, 0 rows affected (0.00 sec)
Statement prepared
mysql> EXECUTE stmt2;
+--------------------+------+------+------+------+-------+
| ifnull(c1, 'total' ) | B1   | B2   | B3   | B4   | TOTAL |
+--------------------+------+------+------+------+-------+
| A1                 |    9 |    2 |    1 |   11 |    23 |
| A2                 |    7 |    9 |    8 |    7 |    31 |
| A3                 |    4 |    8 |    8 |    8 |    28 |
| A4                 |    2 |    5 |    6 |   14 |    27 |
| total              |   22 |   24 |   23 |   40 |   109 |
+--------------------+------+------+------+------+-------+
5 rows in set (0.00 sec)
mysql>

其实数据库中也可以用 CASE WHEN / DECODE 代替 IF

sum(if(c2='B1',C3,0)) AS B1

可改写为

sum(case c2 when 'B1' then C3 else 0 end) AS B1

原文地址：http://blog.chinaunix.net/u3/90603/showart_2017912.html

 

MySQL行列转换实例二

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
create table student
(
     s_name varchar (20),
     s_subject varchar (20),
     s_point int
);
 
insert into student values ( '张三' , '数学' ,86);
insert into student values ( '李四' , '数学' ,78);
insert into student values ( '张三' , '语文' ,67);
insert into student values ( '张三' , '英语' ,90);
insert into student values ( '李四' , '英语' ,88);
insert into student values ( '李四' , '语文' ,70);
insert into student values ( '王五' , '英语' ,90);
insert into student values ( '王五' , '数学' ,88);
insert into student values ( '王五' , '语文' ,70);
 
select * from student;

方法一：

1
2
3
4
5
6
7
8
9
10
11
select n.s_name as '姓名' ,n.shuxue as '数学' ,n.yuwen as '语文' ,n.yingyu as '英语' ,n.pingjun as '平均成绩' ,n.allcount as '总分' from
(
     select s_name as s_name,
     ( select s_point from student where s.s_name=s_name and s_subject= '数学' ) as shuxue,
     ( select s_point from student where s.s_name=s_name and s_subject= '语文' ) as yuwen,
     ( select s_point from student where s.s_name=s_name and s_subject= '英语' ) as yingyu,
     avg (s_point) as pingjun,
     sum (s_point) as allcount
     from student s group by s_name
 
)n order by allcount desc

结果一：

1
2
3
4
5
6
7
8
+--------+--------+--------+--------+--------------+--------+
| 姓名   | 数学   | 语文   | 英语   | 平均成绩     | 总分   |
+--------+--------+--------+--------+--------------+--------+
| 王五   |     88 |     70 |     90 |      82.6667 |    248 |
| 张三   |     86 |     67 |     90 |      81.0000 |    243 |
| 李四   |     78 |     70 |     88 |      78.6667 |    236 |
+--------+--------+--------+--------+--------------+--------+
3 rows in set (0.00 sec)

方法二：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT
     s_name,
     MAX (shuxue) AS 数学,
     MAX (yuwen) AS 语文,
     MAX (yingyu) AS 英语
FROM
(
     SELECT
         s_name,
         CASE s_subject WHEN '数学' THEN s_point END AS shuxue,
         CASE s_subject WHEN '语文' THEN s_point END AS yuwen,
         CASE s_subject WHEN '英语' THEN s_point END AS yingyu
     FROM student
) AS a GROUP BY s_name

结果二：

1
2
3
4
5
6
7
8
+--------+--------+--------+--------+
| s_name | 数学   | 语文   | 英语   |
+--------+--------+--------+--------+
| 张三   |     86 |     67 |     90 |
| 李四   |     78 |     70 |     88 |
| 王五   |     88 |     70 |     90 |
+--------+--------+--------+--------+
3 rows in set (0.00 sec)
u011393661
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
数据库实现行列转换(mysql示例)
09-09
不过，MySQL提供了其他方法来实现行列转换。本文将通过两个示例讲解如何在MySQL中进行行列转换。 **示例一：使用CASE语句** 在MySQL中，可以利用`CASE`语句配合`SUM`或`GROUP BY`来实现行列转换。这种方法适用于...
浅析SQL语句行列转换的两种方法 case…when与pivot函数的应用
12-15
在SQL中，对数据进行行列转换是数据分析过程中常见的需求，特别是在报表展示或数据汇总时。本文将详细探讨两种实现这一转换的方法：`CASE...WHEN` 和 `PIVOT` 函数，通过具体的示例来解析它们的用法。 1. `CASE......
mysql中添加字段
m0_54850467的博客
09-11 4万+
深知大多数初中级Java工程师，想要提升技能，往往是自己摸索成长或者是报班学习，但对于培训机构动则近万的学费，着实压力不小。自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！因此收集整理了一份《Java开发全套学习资料》送给大家，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。
搞定mysql的 行转列（7种方法） 和 列转行
热门推荐
MGmuscler的博客
06-06 5万+
一篇文章搞定mysql的 行转列（7种方法） 和 列转行
Mysql·列转行函数
qq_43308318的博客
04-07 2676
列转行函数 order by 对行内容拍寻 select qname ,GROUP_CONCAT(nu order by rid desc ) from table_name group by qname ;
mysql行列转,mysql行列转换
weixin_29879697的博客
03-18 249
1.一维转二维上图为成绩表中数据，现希望将数据转换为下图。①静态：转化为二维表后的列名及列数是确定不变的，本例中即course只有数学、语文、英语这三门课。select s_name,max(if(course="数学",score,0)) as 数学,max(if(course='语文',score,0)) as 语文,max(if(course='英语',score,0)) as 英语,sum...
MySQL行列转换
weixin_40584932的博客
06-14 540
MySQL几个条件函数一、IF函数 表达式:IF(expr1,expr2,expr3) 满足条件expr1即条件expr1返回true 则expr2否则就是expr3 返回false 作为排序条件使用时 放在order by 后 eg: 1. SELECT IF(1=1,2,3); 则输出2 2.SELECT IF(1=2,2,3);则输出3 select * from test_table or...
mysql 行列动态转换的实现(列联表,交叉表)
09-09
总结来说，MySQL中行列转换的关键在于利用聚合函数、CASE或IF语句、GROUP BY和可能的WITH ROLLUP子句。动态转换适用于未知列数量的情况，而静态转换则适用于已知列情况，可以根据具体需求选择合适的方法。在进行这类...
mysql横向转纵向、纵向转横向排列的方法
12-14
总结一下，MySQL中的行列转换主要依赖于`CASE`、`GROUP BY`、`UNION`等核心SQL语句。通过巧妙地组合运用这些语句，可以灵活地调整数据的布局，从而更好地满足数据分析和展示的需求。在进行转换时，务必注意数据的...
mysql动态行转列
02-04
MySQL 动态行转列是指将数据库中的一行数据转换为多列数据的过程。在本示例中，我们将创建一个存储过程来实现动态行转列，用于从数据库中提取指定学生的成绩信息。 描述解释 MySQL 数据库中存在一个问题，即如何将...
Mysql学习——行与列的多种转换
Helen_1997_1997的博客
12-13 2万+
mysql行与列的多种转换
MySQL行转列函数
死亡阿黑
11-04 2万+
原文链接： http://www.360doc.com/content/18/0525/20/14808334_757019563.shtml 概述 好久没写SQL语句，今天看到问答中的一个问题，拿来研究一下。 问题链接：关于Mysql 的分级输出问题 情景简介 学校里面记录成绩，每个人的选课不一样,而且以后会添加课程，所以不需要把所有课程当作列。数据表里面数据如下图，使用姓名+课程作为联合主键（...
MySQL行列转换函数 -- group_concat
weixin_45873444的博客
06-25 1134
MySQL行列转换函数 – group_concat 1、环境搭建 CREATE TABLE temp_dep( id INT, depId INT, name varchar(20), age INT, address varchar(100), brithday date ); INSERT INTO temp_dep(id, depId, name, age, address, brithday) VALUES (1, 102, '张三', 25, '云南昆明', null); INS
Mysql实现表格的行列转换
m0_48313550的博客
06-18 909
Mysql行列转换
mysql行列转换_数据库实现行列转换(mysql示例)
weixin_35816287的博客
01-18 763
这篇文章通过sql示例代码给大家介绍了mysql数据库如何实现行列转换，下面话不多说，直接来看示例代码吧。原表：表名 :user----------------------------------------name | course | grade----------------------------------------zhangsan | Java | 70--------...
Oracle UNPIVOT-列转行函数
qq_35354529的博客
07-28 1748
语法：unpivot(新增值所在列的列名 for 新增列转为行后所在列的列名 in (需转为行的列名))； 执行原理：将pivot函数或unpivot函数接在查询结果集的后面。相当于对结果集进行处理。 测试数据 With ft as ( select 1 id,'路飞' name_, 17 age, '无厘头船长' position_,170 height_ from dual Union se...
Mysql之一行转多行（列转行）--- help_topic
J_M_S_H_T的博客
11-04 5253
用到的知识点： substring_index() length() mysql.help_topic 原数据如下，将jsondata转成多行，以逗号分割 SELECT tp.id, substring_index( SUBSTRING_INDEX( tp.jsondata, ',', hp.help_topic_id + 1 ), ',', - 1 ) json FROM testjson tp LEFT JOIN mysql.help_topic hp ON hp.help_t
mysql 行列转换
最新发布
05-26
MySQL 中可以使用 `CASE WHEN` 和 `GROUP BY` 语句来实现行列转换。 首先，我们需要使用 `CASE WHEN` 语句将每一行的值转换为我们需要的列。例如，假设我们有一个 `orders` 表，其中包含订单号、客户姓名和订单金额： ``` +------------+---------------+--------+ | order_id | customer_name | amount | +------------+---------------+--------+ | 1 | Alice | 100 | | 2 | Bob | 200 | | 3 | Alice | 150 | | 4 | Bob | 300 | +------------+---------------+--------+ ``` 现在我们想要将客户姓名作为列，订单金额作为值进行行列转换。我们可以使用以下 SQL 语句： ``` SELECT order_id, SUM(CASE WHEN customer_name = 'Alice' THEN amount ELSE 0 END) AS Alice, SUM(CASE WHEN customer_name = 'Bob' THEN amount ELSE 0 END) AS Bob FROM orders GROUP BY order_id; ``` 执行以上 SQL 语句后，将得到以下结果： ``` +------------+-------+-----+ | order_id | Alice | Bob | +------------+-------+-----+ | 1 | 100 | 0 | | 2 | 0 | 200 | | 3 | 150 | 0 | | 4 | 0 | 300 | +------------+-------+-----+ ``` 以上 SQL 语句中，我们使用了 `SUM` 函数来计算每个客户的订单金额，如果客户姓名不是目标客户，则将金额设置为 0。 这样，我们就成功地将行列转换为了我们需要的形式。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值