mysql行列转换方法总结

最新推荐文章于 2024-04-06 20:56:42 发布
最新推荐文章于 2024-04-06 20:56:42 发布
阅读量640 收藏
点赞数
分类专栏： MySQL 文章标签： 数据库 mysql sql server
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/huanglei0809/article/details/32104455
版权


转 http://bbs.csdn.net/topics/310045927

在某些数据库中有交叉表，但在MySQL中却没有这个功能，但网上看到有不少朋友想找出一个解决方法，特集思广义。无论对错皆有分。

数据样本：
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);

结果 (可不带行/列汇总)
[code=BatchFile]+------+-----+-----+-----+-----+------+
|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   |
+------+-----+-----+-----+-----+------+[/code]


方法一：

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)


方法二：

静态：
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



方法三：

动态：
SET @EE='';
SELECT @EE:=CONCAT(@EE,'SUM(IF(C2=\'',C2,'\'',',C3,0)) AS ',C2,',') FROM 
(SELECT DISTINCT C2 FROM TX) A;
SET @QQ=CONCAT('SELECT ifnull(c1,\'total\'),',LEFT(@EE,LENGTH(@EE)-1),' ,SUM(C3) AS TOTAL FROM TX GROUP BY C1 WITH ROLLUP');
PREPARE stmt2 FROM @QQ;
EXECUTE stmt2;


方法四：


--木有合计,在SQL Server下测试通过

if object_id('tempdb..#tx') is not null drop table #tx
go
create table #tx(
id int primary key,
c1 char(2),
c2 char(2),
c3 int
);

insert into #tx (id,c1,c2,c3) values (1 ,'A1','B1',9)
insert into #tx (id,c1,c2,c3) values (2 ,'A2','B1',7)
insert into #tx (id,c1,c2,c3) values (3 ,'A3','B1',4)
insert into #tx (id,c1,c2,c3) values (4 ,'A4','B1',2)
insert into #tx (id,c1,c2,c3) values (5 ,'A1','B2',2)
insert into #tx (id,c1,c2,c3) values (6 ,'A2','B2',9)
insert into #tx (id,c1,c2,c3) values (7 ,'A3','B2',8)
insert into #tx (id,c1,c2,c3) values (8 ,'A4','B2',5)
insert into #tx (id,c1,c2,c3) values (9 ,'A1','B3',1)
insert into #tx (id,c1,c2,c3) values (10 ,'A2','B3',8)
insert into #tx (id,c1,c2,c3) values (11 ,'A3','B3',8)
insert into #tx (id,c1,c2,c3) values (12 ,'A4','B3',6)
insert into #tx (id,c1,c2,c3) values (13 ,'A1','B4',8)
insert into #tx (id,c1,c2,c3) values (14 ,'A2','B4',2)
insert into #tx (id,c1,c2,c3) values (15 ,'A3','B4',6)
insert into #tx (id,c1,c2,c3) values (16 ,'A4','B4',9)
insert into #tx (id,c1,c2,c3) values (17 ,'A1','B4',3)
insert into #tx (id,c1,c2,c3) values (18 ,'A2','B4',5)
insert into #tx (id,c1,c2,c3) values (19 ,'A3','B4',2)
insert into #tx (id,c1,c2,c3) values (20 ,'A4','B4',5)


SELECT     c1
          ,sum(CASE
                   WHEN c2='B1' THEN c3
                   ELSE 0
               END) AS [b1]
          ,sum(CASE
                   WHEN c2='B2' THEN c3
                   ELSE 0
               END) AS [b2]
          ,sum(CASE
                   WHEN c2='B3' THEN c3
                   ELSE 0
               END) AS [b3]
          ,sum(CASE
                   WHEN c2='B4' THEN c3
                   ELSE 0
               END) AS [b4]
FROM       #tx
GROUP BY   c1



方法五：


--动态的话

if object_id('tempdb..#tx') is not null drop table #tx
go
create table #tx(
id int primary key,
c1 char(2),
c2 char(2),
c3 int
);

insert into #tx (id,c1,c2,c3) values (1 ,'A1','B1',9)
insert into #tx (id,c1,c2,c3) values (2 ,'A2','B1',7)
insert into #tx (id,c1,c2,c3) values (3 ,'A3','B1',4)
insert into #tx (id,c1,c2,c3) values (4 ,'A4','B1',2)
insert into #tx (id,c1,c2,c3) values (5 ,'A1','B2',2)
insert into #tx (id,c1,c2,c3) values (6 ,'A2','B2',9)
insert into #tx (id,c1,c2,c3) values (7 ,'A3','B2',8)
insert into #tx (id,c1,c2,c3) values (8 ,'A4','B2',5)
insert into #tx (id,c1,c2,c3) values (9 ,'A1','B3',1)
insert into #tx (id,c1,c2,c3) values (10 ,'A2','B3',8)
insert into #tx (id,c1,c2,c3) values (11 ,'A3','B3',8)
insert into #tx (id,c1,c2,c3) values (12 ,'A4','B3',6)
insert into #tx (id,c1,c2,c3) values (13 ,'A1','B4',8)
insert into #tx (id,c1,c2,c3) values (14 ,'A2','B4',2)
insert into #tx (id,c1,c2,c3) values (15 ,'A3','B4',6)
insert into #tx (id,c1,c2,c3) values (16 ,'A4','B4',9)
insert into #tx (id,c1,c2,c3) values (17 ,'A1','B4',3)
insert into #tx (id,c1,c2,c3) values (18 ,'A2','B4',5)
insert into #tx (id,c1,c2,c3) values (19 ,'A3','B4',2)
insert into #tx (id,c1,c2,c3) values (20 ,'A4','B4',5)

select * from #tx

declare @sql varchar(8000)
set @sql='select c1,'
select @sql=@sql+'sum(case when c2='''+cast(c2 as varchar(10))+''' then c3 else 0 end)['+cast(c2 as varchar(10))+'],'
from (select distinct c2 from #tx ) a
print(@sql)

set @sql=left(@sql,len(@sql)-1)+' from #tx group by c1'
exec(@sql)
if object_id('tempdb..#tx') is not null drop table #tx

/*
A1 9 2 1 11
A2 7 9 8 7
A3 4 8 8 8
A4 2 5 6 14
*/



测试

1、

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、

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、

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、
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>




5、


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>





X-X
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mysql行列转换
03-02
java语言 数据库 mysql数据库 sql行列转换的例子 详解
数据库实现行列转换(mysql示例)
09-09
不过，MySQL提供了其他方法来实现行列转换。本文将通过两个示例讲解如何在MySQL中进行行列转换。 **示例一：使用CASE语句** 在MySQL中，可以利用`CASE`语句配合`SUM`或`GROUP BY`来实现行列转换。这种方法适用于...
mysql中行列表示_mysql行列转换方法总结
weixin_39531582的博客
01-18 394
数据样本：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...
mysql中的行列转换
qq_45624685的博客
12-07 2198
mysql中的行列转换
mysql中行列表示_mysql 行列转换怎么写？
weixin_33050117的博客
01-18 190
一段sqlserversql代码完成行列转换，但在mysql里无法执行，请问mysql的应该怎么写？createtableA1(NO1int,NO2varchar(10),MYA1int,MYA2int,MYA3int,MYA4int,MYA5int,MYA6int)insertA1...一段sqlserver sql代码完成行列转换，但在mysql里无法执行，请问mysql的应该怎么写？crea...
mysql 行 列_MySQL-行到列
weixin_42500625的博客
01-25 381
匿名用户我将对解决这个问题的步骤进行更长更详细的解释。 如果太长了我很抱歉。我将从你给出的基础开始，并用它来定义几个术语，我将在这篇文章的其余部分中使用这些术语。 这将是基表:select * from history;+--------+----------+-----------+| hostid | itemname | itemvalue |+--------+----------+---...
mysql 行列动态转换的实现(列联表,交叉表)
09-09
总结来说，MySQL中行列转换的关键在于利用聚合函数、CASE或IF语句、GROUP BY和可能的WITH ROLLUP子句。动态转换适用于未知列数量的情况，而静态转换则适用于已知列情况，可以根据具体需求选择合适的方法。在进行这类...
mysql横向转纵向、纵向转横向排列的方法
12-14
总结一下，MySQL中的行列转换主要依赖于`CASE`、`GROUP BY`、`UNION`等核心SQL语句。通过巧妙地组合运用这些语句，可以灵活地调整数据的布局，从而更好地满足数据分析和展示的需求。在进行转换时，务必注意数据的...
浅析SQL语句行列转换的两种方法 case…when与pivot函数的应用
12-15
在SQL中，对数据进行行列转换是数据分析过程中常见的需求，特别是在报表展示或数据汇总时。本文将详细探讨两种实现这一转换的方法：`CASE...WHEN` 和 `PIVOT` 函数，通过具体的示例来解析它们的用法。 1. `CASE......
mysql动态行转列
02-04
MySQL 动态行转列是指将数据库中的一行数据转换为多列数据的过程。在本示例中，我们将创建一个存储过程来实现动态行转列，用于从数据库中提取指定学生的成绩信息。 描述解释 MySQL 数据库中存在一个问题，即如何将...
通过SQL语句实现行列转换的几种方法
12-18
1. 列转行 2. 行转列 3. 多列转换成字符串 4. 多行转换成字符串 5. 字符串转换成多列 6. 字符串转换成多行
搞定mysql的 行转列（7种方法） 和 列转行
最新发布
JoeBlackZQQ的专栏
04-06 1834
比较抽象，难以理解。（2）IF(subject=‘语文’,score,0) 作为条件，即对所有subject='语文’的记录的score字段进行SUM()、MAX()、MIN()、AVG()操作，如果score没有值则默认为0。INSERT INTO tb_score(userid,subject,score) VALUES ('001','语文',90);INSERT INTO tb_score(userid,subject,score) VALUES ('003','语文',70);
MYSQL 行转列、列转行、多列转一行、一行转多列
老王玩编程
06-10 1万+
版本说明mysql8.0selectname,max(case subject when '语文' then resuilt else 0 end) '语文',max(case subject when '数学' then resuilt else 0 end) '数学',max(case subject when '物理' then resuilt else 0 end) '物理'from student group by name大体同case whenselect name,max(if(subjec
【MySQL】数据表行列转换
花北城的博客
08-28 1万+
表行列转置，顾明思议，将表格中一行行的数据，采用SQL脚本转换成列数据。
Mysql实现行列转换
热门推荐
蓝天⊙白云的博客
02-15 4万+
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 C# from test group by name 方案二： select distinct c.`name` AS name, (s
mysql之行列转换
一剑轻语
04-27 1万+
目的：实现行列转换功能需求：导出成如下图的Excel数据：数据库格式如下图除报销表外还涉及到用户表、部门表、等许多从表我们需要将数据进行筛选（报销成功的），合并，行列转换1.筛选并合并SELECT su.name AS "id",ed.cost_type,ed.cost_description,sum(ed.amount_money) FROM expense_detail ed  LEFT JO...
SQL面试题：有A B C三列,用SQL语句实现：当A列大于B列时选择A列否则选择B列
07-29 2万+
.请教一个面试中遇到的SQL语句的查询问题 表中有A B C三列,用SQL语句实现：当A列大于B列时选择A列否则选择B列，当B列大于C列时选择B列否则选择C列。 ------------------------------------------ select (case when a>b then a else b end ), (case when b>c then b esle c
mysql的行列互转
Don't Call Me
08-22 1838
行转列： 假如现在你有这样一张表， id name course score 1 张三 数学 34 2 张三 语文 55 3 李四 数学 66 4 李四 语文 77 5 王五 数学 88 6 王五 语文 99 你想运用行转列将表格变成下面这样...
mysql 行列转换
05-26
MySQL 中可以使用 `CASE WHEN` 和 `GROUP BY` 语句来实现行列转换。 首先，我们需要使用 `CASE WHEN` 语句将每一行的值转换为我们需要的列。例如，假设我们有一个 `orders` 表，其中包含订单号、客户姓名和订单金额...

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值