SQL中如何将行转成列详解
我们以MySQL数据库为例,来说明行转列的实现方式。
首先,假设我们有一张分数表(tb_score),表中的数据如下图:
然后,我们再来看一下转换之后需要得到的结果,如下图:
可以看出,这里行转列是将原来的subject字段的多行内容选出来,作为结果集中的不同列,并根据userid进行分组显示对应的score。通常,我们有两种方式来实现这种转换。
[var] SELECT userid,SUM(CASE `subject` WHEN 语文 THEN score ELSE 0 END) as 语文 ,
SUM(CASE `subject` WHEN 数学 THEN score ELSE 0 END) as 数学 ,
SUM(CASE `subject` WHEN 英语 THEN score ELSE 0 END) as 英语 ,
SUM(CASE `subject` WHEN 政治 THEN score ELSE 0 END) as 政治
FROM tb_score
GROUP BY userid
注意,SUM() 是为了能够使用GROUP BY根据userid进行分组,因为每一个userid对应的
subject= 语文 的记录只有一条,所以SUM() 的值就等于对应那一条记录的score的值。假如userid = 001 and subject= 语文 的记录有两条,则此时SUM() 的值将会是这两条记录的和,同理,使用Max()的值将会是这两条记录里面值最大的一个。但是正常情况下,一个user对应一个subject只有一个分数,因此可以使用SUM()、MAX()、MIN()、AVG()等聚合函数都可以达到行转列的效果。
SUM(IF(`subject`= 语文 ,score,0)) as 语文 ,
SUM(IF(`subject`= 数学 ,score,0)) as 数学 ,
SUM(IF(`subject`= 英语 ,score,0)) as 英语 ,
SUM(IF(`subject`= 政治 ,score,0)) as 政治
FROM tb_score
GROUP BY userid
注意, IF(subject= 语文 ,score,0) 作为条件,即对所有subject= 语文 的记录的score字段进行SUM()、MAX()、MIN()、AVG()操作,如果score没有值则默认为0。
[var]列转行是上述过程的逆过程,所以其思路也比较直观:
行记录由一行变为多行,列字段由多列变为单列; 一行变多行需要复制,列字段由多列变单列相当于是堆积的过程,其实也可以看做是复制; 一行变多行,那么复制的最直观实现当然是使用union,即分别针对每门课程提取一张衍生表,最后将所有课程的衍生表union到一起即可,其中需要注意字段的对齐按照这一思路,给出SQL实现如下:
SELECT uid,sum(if(course= 语文 , score, NULL)) as `语文`, sum(if(course= 数学 , score, NULL)) as `数学`, sum(if(course= 英语 , score, NULL)) as `英语`, sum(if(course= 物理 , score, NULL)) as `物理`,
sum(if(course= 化学 , score, NULL)) as `化学`FROM scoreLongGROUP BY uid
查询结果当然是预期的长表。这里重点解释其中的三个细节:
在每个单门课的衍生表中,例如这句:SELECT uid, 语文 as course, 语文 as score,用单引号包裹起来的课程名称是字符串常量,比如语文课的衍生表中的课程名都叫语文,然后将该列命名为course;第二个用反引号包裹起来的课程名实际上是从宽表中引用这一列的取值,然后将其命名为score。
这实际上对应的一个知识点是:在SQL中字符串的引用用单引号(其实双引号也可以),而列字段名称的引用则是用反引号.
上述用到了where条件过滤成绩为空值的记录,这实际是由于在原表中存在有空值的情况,如不加以过滤则在本例中最终查询记录有10条,其中两条记录的成绩字段为空
最后,本例中用union关键字实现了多表的纵向拼接,实际上用union all更为合理,二者的区别是union会完成记录去重;而union all则简单的拼接,在确定不存在重复或无需去重的情况下其效率更高。
[var]到此这篇关于SQL中如何将行转成列的文章就介绍到这了,更多相关SQL将行转成列内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持!
我想要获取技术服务或软件
服务范围:MySQL、ORACLE、SQLSERVER、MongoDB、PostgreSQL 、程序问题
服务方式:远程服务、电话支持、现场服务,沟通指定方式服务
技术标签:数据恢复、安装配置、数据迁移、集群容灾、异常处理、其它问题
本站部分文章参考或来源于网络,如有侵权请联系站长。
数据库远程运维 SQL中如何将行转成列详解
相关文章
- 哪些异常是RuntimeException?Sql异常属于RuntimeException吗?Spring下SQL异常事务回滚
- mysql查看查询慢的语句_sql慢查询如何优化
- SQL基础编程_如何学编程
- mysql的SQL_NO_CACHE(在查询时不使用缓存)和sql_cache用法详解数据库
- MySQL创建表:SQL语句实现(mysql创建表的sql语句)
- 如何使用SQL Server导出数据(sqlserver导出数据sql)
- Oracle如何导入SQL文件(oracle导入sql文件)
- Oracle 视图 V$SQL_CS_HISTOGRAM 官方解释,作用,如何使用详细说明
- sql掌握MySQL,才能写出精彩的SQL语句(mysql不等于如何写)
- 如何使用MySQL查询并分析SQL计划(mysql查询sql计划)
- 「MySQL 教程」学习如何拼接 SQL 语句,让你的操作更灵活高效(mysql拼接sql语句)
- Oracle数据库如何导入SQL表(oracle导入sql表)
- 快速上手:如何打开MySQL SQL命令行界面?(mysql打开sql)
- SQL Server查询:如何处理锁表问题(sqlserver查询锁表)
- 个人租用MS SQL数据库实现诸多有用功能(个人租用mssql数据库)
- 如何在windows xp上安装SQL Server(xp装sqlserver)
- Oracle数据库:SQL文件的执行步骤详解(oracle如何执行sql文件)
- 如何使用MySQL定时自动执行SQL语句(mysql定时执行sql)
- MSSQL如何通过美化格式简化SQL查询(mssql美化格式sql)
- SQLserver如何快速导入SQL文件(sqlserver导入sql文件)
- 如何将SQL文件导入MySQL(将sql文件导入mysql)
- MySQL如何运行SQL文件(mysql运行sql文件)
- Oracle SQL语句实现列数据修改(oracle修改列sql)
- MySQL数据上传如何处理大型SQL文件大小(mysql上传sql大小)
- 查询Oracle数据库也支持SQL查询(oracle也支持sql)
- Oracle SQL解锁数据库的最强利器(oracle sql文)
- Oracle SQL字符串截断技术研究(oracle sql截断)
- MySQL优化之如何了解SQL的执行频率