首页 / MYSQL / mysql行列转换方法总结

mysql行列转换方法总结

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了mysql行列转换方法总结，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含9709字，纯文字阅读大概需要14分钟。

内容图文

转 http://bbs.csdn.net/topics/310045927 在某些数据库中有交叉表，但在MySQL中却没有这个功能，但网上看到有不少朋友想找出一个解决方法，特集思广义。无论对错皆有分。数据样本： createtabletx( idintprimarykey, c1char(2), c2char(2), c3int ); inser

转 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]

方法一：

"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
);

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>

内容总结

以上是互联网集市为您收集整理的mysql行列转换方法总结全部内容，希望文章能够帮你解决mysql行列转换方法总结所遇到的程序开发问题。如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容，请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/564071.html

来源：【匿名】

【上一篇】因为mysql多了个piress用户，关闭远程连接【下一篇】用IE远程创建Mysql数据库的简易程序

更多 ►

【mysql行列转换方法总结】教程文章相关的互联网学习教程文章

mysql 文件导入方法总结

数据导入3三种方法一、phpMyAdmin限制大小：2M1.创建数据库2.导入.sql或.sql.zip文件大数据导入方法一：http://jingyan.baidu.com/article/9113f81bcdf3a32b3214c7e7.html大数据导入方法二：http://jingyan.baidu.com/article/60ccbceb21966d64cab1979e.html参考文档http://www.sjyhome.com/php/phpmyadmin-limit.html打开PHP配置文件 php.ini查找 upload_max_filesize 和 post_max_size 把他们的值修改的大一点如果上传的文件很大...

转：C++操作mysql方法总结（1)【代码】【图】

原文：http://www.cnblogs.com/joeblackzqq/p/4332945.htmlC++通过mysql的c api和通过mysql的Connector C++ 1.1.3操作mysql的两种方式? ?使用vs2013和64位的msql 5.6.16进行操作? ?? ?项目中使用的数据库名为booktik 表为book ………. （共有30条记录，只列出了部分记录，14-30未列出）一、通过mysql的C api进行操作? ?1、新建一个空项目? ?2、将D:\Program Files\MySQL\MySQL Server 5.6\include添加到项目的包含目录中（根据具体...

C++操作mysql方法总结（2）【代码】【图】

C++通过ODBC和通过MFC ODBC操作mysql的两种方式使用vs2013和64位的msql 5.6.16进行操作项目中使用的数据库名和表数据请参考C++操作mysql方法总结（1)中的介绍既然使用到了ODBC那么就要需要添加数据源了控制面板->管理工具->数据源->用户DSN->添加填写相关的数据库连接配置和使用的数据库，可点击Test进行测试，查看是否成功连接，设置完成后点击OK其中Data Source Name即MySqlODBC我们会在连接数据库时用到至此，数据源添加完毕...

Hadoop-Streaming实战经验及问题解决方法总结

目录 1. ? Join操作分清join的类型很重要 2. ?启动程序中key字段和partition字段的设定 3. ?控制hadoop程序内存的方法 4. ? 对于数字key的排序问题 5. ? 在mapper中获取map_input_file环境变量的方法 6. ? 运行过程中记录数据的方法 7. ?多次运行Hadoop之是目录 1. ? Join操作分清join的类型很重要… 2. ?启动程序中key字段和partition字段的设定… 3. ?控制hadoop程序内存的方法… 4. ? 对于数字key的排序问题… 5. ? 在mapper中获...

SQL日志清除常用的方法总结

文章总结了多种SQL 日志清除常用的方法，有需要的朋友可参考一下本文章。1 首先确认的是你能直接删除，他们是SQL SERVER 的错误日志，而不是数据库日志。 2 日志过大说明你没有截断错误日志，错误日志是可以截断的，进入你的数据库输入DBCC ERRORLOG 每执行一次，当前的错误日志推出，让后建立新的错误日志，你只能删除 ERRORLOG1 --- 6的错误日志没有号码的是正在使用的日志，删除会报错，如果它比较大，就DBCC ERRORLOG，而后他...

mysql导入导出数据中文乱码解决方法总结

本文章总结了mysql导入导出数据中文乱码解决方法，出现中文乱码一般情况是导入导入时编码的设置问题，我们只要把编码调整一致即可解决此方法，下面是百度搜索到的一些方法总结。linux系统中 linux默认的是utf8编码，而windows是gbk编码，所以会出现上面的乱码问题。解决mysql导入导出数据乱码问题首先要做的是要确定你导出数据的编码格式，使用mysqldump的时候需要加上--default-character-set=utf8，例如下面的代码：代码如下 ...

mysql查找删除表中重复数据方法总结【图】

在数据库表里，我们有时候会保存了很多重复的数据，这些重复的数据浪费资源，我们要将其删除掉，应该怎么处理呢？下面来看一下。先看下我们的表数据，有一些数据是重复的。要查找重复数据，我们可以使用mysql里的having语句，如图。执行这个语句后，我们可以看到现在的结果里显示的就是表中重复数据的字段。要删除这些重复的数据，我们找出这些数据的ID，在select语句里，添加id字段，使用max函数，可以得到重复数据最后面的id。执...

SQL有效防止SQL注入方法总结？

文章从数据库服务器本身讲防止SQL注入设置以及在程序中关于防止SQL注入一些参数过滤方法总结，有需要学习的同学可参考一下。1.不要使用sa用户连接数据库 2.新建一个public权限数据库用户，并用这个用户访问数据库 3.[角色]去掉角色public对sysobjects与syscolumns对象的select访问权限 4.[用户]用户名称-> 右键－属性－权限－在sysobjects与syscolumns上面打“” 5.在SQL查询分析器中执行以下代码（失败表示权限正确，如能显示...

怎样修改无线路由器密码MySQL修改密码方法总结

方法一使用phpmyadmin，这是最简单的了，修改mysql库的user表，不过别忘了使用PASSWORD函数。方法二使用mysqladmin，这是前面声明的一个特例。 mysqladmin -u root -p password mypasswd 输入这个命令后，需要输入root的原密码，然后root的密码将改为mypasswd。把命令里的root改为你的用户名，你就可以改你自己的密码了。当然如果你的mysqladmin连接不上mysql server，或者你没有办法执行mysqladmin，那么这种方法...

利用php查询mysql表是否存在的方法总结

这篇文章主要介绍了php检测mysql表是否存在的方法,结合实例形式总结分析了php使用pdo连接及mysql函数实现针对mysql表存在的判断方法,需要的朋友可以参考下本文实例讲述了php检测mysql表是否存在的方法。分享给大家供大家参考，具体如下：pdo:<?php $dsn = mysql:dbname=test;host=127.0.0.1; $user = root; $password = ; try {$pdo = new PDO($dsn, $user, $password); } catch (PDOException $e) {die("数据库连接失败".$e->getM...

MySQL修改密码方法总结_PHP教程

php无法连接mysql问题解决方法总结_PHP教程

本文章总结了在php开发中可能会常常碰到的一些php连接不了mysql数据库的一些问题总结与解决方法分享，有需要的朋友可参考一下。问题一今天给本本装上mysql和php，一切安装都很正常（php@IIS7.5-fastCGI）。但用phpmyadmin连接测试时却出现“#2002无法连接”的错误，用其它程序也是同样问题。检查了下，php的mysql扩展正确安装，mysql服务正常启动--那就奇了怪了。又用navicat连接，居然连上了。。看来是php和mysql间的通讯出了问...

MySQLserverhasgoneaway问题的解决方法总结

mysql出现ERROR : (2006, 'MySQL server has gone away') 的问题意思就是指client和MySQL server之间的链接断开了。造成这样的原因一般是sql操作的时间过长，或者是传送的数据太大(例如使用insert ... values的语句过长，这种情况可以通过修改max_allowed_packed的配置参数来避免，也可以在程序中将数据分批插入)。产生这个问题的原因有很多，总结下网上的分析：原因一. MySQL 服务宕了判断是否属于这个原因的方法很简单，进入mys...

MySQL修改密码方法总结_php技巧

MySQL修改密码方法总结

MYSQL - 技术教程分类

MySQL 教程 MySQL 安装 MySQL 管理 MySQL PHP 语法 MySQL 连接 MySQL 创建数据库 MySQL 删除数据库 MySQL 选择数据库 MySQL 数据类型 MySQL 创建数据表 MySQL 删除数据表 MySQL 插入数据 MySQL 查询数据 MySQL WHERE 子句 MySQL UPDATE 更新 MySQL DELETE 语句 MySQL LIKE 子句 MySQL UNION MySQL 排序 MySQL 分组 MySQL 连接的使用 MySQL NULL 值处理 MySQL 事务 MySQL ALTER命令 MySQL 索引 MySQL 临时表 MySQL 复制表 MySQL 元数据 MySQL 序列使用 MySQL 处理重复数据 MySQL 及 SQL 注入 MySQL 导出数据 MySQL 导入数据 MySQL 函数 MySQL 运算符 mysql 全部

MYSQL - 最热教程

sql分组取最大记录方法 mysql如何设置默认值 mysql创建数据表时指定默认值教程 MySQL 5.7 的初始化操作（root初始密码...Oracle中合并数据集(多行变一行)mysql 相同内容的字段合并为一条的方法解决mysql设置时区时的错误Unknown or ...解决ubuntu下mysql的'Access denied fo...ubuntu系统中MysqlERROR1045(28000)报错...mysql数据库设置不区分大小写

首页 / MYSQL / mysql行列转换方法总结

mysql行列转换方法总结

内容导读

内容图文

内容总结

内容备注

内容手机端

【mysql行列转换方法总结】教程文章相关的互联网学习教程文章

mysql 文件导入方法总结

转：C++操作mysql方法总结（1)【代码】【图】

C++操作mysql方法总结（2）【代码】【图】

Hadoop-Streaming实战经验及问题解决方法总结

SQL日志清除常用的方法总结

mysql导入导出数据中文乱码解决方法总结

mysql查找删除表中重复数据方法总结【图】

SQL有效防止SQL注入方法总结？

怎样修改无线路由器密码MySQL修改密码方法总结

利用php查询mysql表是否存在的方法总结

MySQL修改密码方法总结_PHP教程

php无法连接mysql问题解决方法总结_PHP教程

MySQLserverhasgoneaway问题的解决方法总结

MySQL修改密码方法总结_php技巧

MySQL修改密码方法总结

MYSQL - 相关标签

MYSQL - 技术教程分类

MYSQL - 最新教程

MYSQL - 最热教程