MySQL实现行列转换

实际应用中,会遇到需要把表的某些行转换成列,或者把列转换成行的情况。比如一张表在数据库中是这样的:

图1

但是,需要的结果可能是这样:

图2

这个时候就得行列转换了。

1.行转列的几种方法

1.1 case... when ... then ... else ... end

select uname,uid, -- 正常查询的字段
sum(
case
when course ='英语' then score -- 需要转换的字段
else 0
end) '英语',
sum(
case
when course= '物理' then score
else 0
end) '物理',
sum(
case
when course='化学' then score
else 0
end) '化学'
from course
group by uid

另一种写法:

case course
when '化学' then score
else 0
end

另外若省略‘else 0‘,则没有该课程的同学的分数会填充为null; sum替换成max结果一样。

1.2 if (`字段名1`=‘字段值’,,)

select uname,uid,
sum(if(`course`='英语',score,0)) '英语',
sum(if(`course`='物理',score,0)) '物理',
sum(if(`course`='化学',score,0)) '化学'
from course
group by uname

貌似比第一种方法简洁一些,所以下面的扩展是基于这种方法的~

以上两种转换方法结果相同,如图2。另外实际应用中还可能需要有总计的结果,如图3.

图3

total这一列简单,直接在之前的查询基础上加一个sum(score) 'total'即可;Total这一行则可以看成之前的查询不加group by而聚合成一行。因此可以看做是两个表组合到一起如图4和图5:

图4

图5

1.3 if (`字段名1`=‘字段值’,,) + union

把两个查询结果拼接到一起就是图3的样子了,代码如下:

select uid,uname,
sum(if(`course`='英语',score,0)) '英语',
sum(if(`course`='物理',score,0)) '物理',
sum(if(`course`='化学',score,0)) '化学',
sum(score) 'total'
from course
group by uname
union
select 'Total',null,
sum(if(`course`='英语',score,0)) '英语',
sum(if(`course`='物理',score,0)) '物理',
sum(if(`course`='化学',score,0)) '化学',
sum(score) 'total'
from course

1.4 if (`字段名1`=‘字段值’,,) + IFNULL()+with rollup

这种方法效果同1.3,

select ifnull(uid,'Total') uid, uname,
sum(if(`course`='英语',score,0)) '英语',
sum(if(`course`='物理',score,0)) '物理',
sum(if(`course`='化学',score,0)) '化学',
sum(score) 'total'
from course
group by uid
with ROLLUP

比1.3简洁一些,效率应该也高一点。with rollup和group by配套使用,会在已有的查询结果上再多出一行,对结果再聚合成一行,即图5的那一行,若不是数字类型,则返回最下面一行的数据,最后一行分组的字段会显示null,因此在配合ifnull()就可以了。

有瑕疵,想把它变成 null,有待完善。

2.列转行

列转行刚好和行转列情况相反,即:

数据库中存储的是这样

图2

而我们需要这样的结果

图1

没有在创建新表,暂且把这个视图当成新表吧

create view rtc as
select ifnull(uid,'Total') uid,uname,
sum(if(`course`='英语',score,0)) '英语',
sum(if(`course`='物理',score,0)) '物理',
sum(if(`course`='化学',score,0)) '化学',
sum(score) 'total'
from course
group by uid
with ROLLUP
-- 下面是列转行代码
select uid,uname,'英语' course,英语 score from rtc where uid <>'Total' and 英语>0
union all select uid,uname,'物理' ,物理 from rtc where uid <>'Total' and 物理>0
union all select uid,uname,'化学' ,化学 from rtc where uid <>'Total' and 化学>0

到此这篇关于MySQL实现行列转换的文章就介绍到这了。希望对大家的学习有所帮助,也希望大家多多支持我们。

(0)

相关推荐

  • mysql 行列转换的示例代码

    一.需求 我们有三张表,我们需要分类统计一段时间内抗生素的不同药敏结果,即 report_item_drugs 表的 drugs_result, 在不同项目project_name 和不同抗生素 antibiotic_dict_name 下的占比,并将药敏结果显示在行上,效果如下: 三张原始表(仅取需要的字段示例),分别是: 报告表 项目表 抗生素表(药敏结果drugs_result为一列值) 二.实现 1.按照项目.抗生素分组求出检出的总数 SELECT A.project_name,A.an

  • 数据库实现行列转换(mysql示例)

    这篇文章通过sql示例代码给大家介绍了mysql数据库如何实现行列转换,下面话不多说,直接来看示例代码吧. 原表: 表名 :user ---------------------------------------- name | course | grade ---------------------------------------- zhangsan | Java | 70 ---------------------------------------- zhangsan | C++ |

  • mysql 行列动态转换的实现(列联表,交叉表)

    (1)动态,适用于列不确定情况 create table table_name( id int primary key, col1 char(2), col2 char(2), col3 int ); insert into table_name 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','B

  • MySQL实现行列转换

    实际应用中,会遇到需要把表的某些行转换成列,或者把列转换成行的情况.比如一张表在数据库中是这样的: 图1 但是,需要的结果可能是这样: 图2 这个时候就得行列转换了. 1.行转列的几种方法 1.1 case... when ... then ... else ... end select uname,uid, -- 正常查询的字段 sum( case when course ='英语' then score -- 需要转换的字段 else 0 end) '英语', sum( case when

  • Oracle实现行列转换的方法分析

    本文实例讲述了Oracle实现行列转换的方法.分享给大家供大家参考,具体如下: 1.固定列数的行列转换 如: student subject grade --------- ---------- -------- student1 语文 80 student1 数学 70 student1 英语 60 student2 语文 90 student2 数学 80 student2 英语 100 -- 转换为: 语文 数学 英语 student1 80 70 60 student2 90 80 10

  • C#中DataTable实现行列转换的方法

    本文实例讲述了C#中DataTable实现行列转换的方法.分享给大家供大家参考.具体实现方法如下: protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { DataTable tt = GetCrossTable(CreateDT()); GridView1.DataSource = tt; GridView1.DataBind(); } } //创建DataTable protected DataTab

  • MySQL从MyISAM转换成InnoDB错误与常用解决办法

    原来自己用的是为了装的, 所以在设置database usage(如下图1)的时候按照discuz官方的建议,选的都是Non-Transactional Database Only(只支持MyISAM数据引擎的非事务数据库),用MyISAM数据库,还没涉及到需要InnoDB,因此打算直接不加载INNODB引擎.后来在做WordPress,一开始还不知道原来WordPress用的是InnoDB数据引擎,于是在原来的数据库里面就建了一个数据库,一开始也没发觉问题,安装,导入sql,都没问题,当时也没

  • 一个简单的SQL 行列转换语句

    一个简单的SQL 行列转换 Author: eaglet 在数据库开发中经常会遇到行列转换的问题,比如下面的问题,部门,员工和员工类型三张表,我们要统计类似这样的列表 部门编号 部门名称 合计 正式员工 临时员工 辞退员工 1 A 30 20 10 1 这种问题咋一看摸不着头绪,不过把思路理顺后再看,本质就是一个行列转换的问题.下面我结合这个简单的例子来实现行列转换. 下面3张表 复制代码 代码如下: if exists ( select * from sysobjects where id =

  • Sql实现行列转换方便了我们存储数据和呈现数据

    从MS Sql Server 2005微软就推出了pivot和unpivot实现行列转换,这极大的方便了我们存储数据和呈现数据.今天就对这两个关键字进行分析,结合实例讲解如何存储数据,如何呈现数据. 例如学生选课和成绩系统中就有一张表,该表存储了学生的课程成绩,我们无法去预料课程的多少,因此一般表会设计为下面这样: 图1  最后一列是课程编号,这样无论开学之后还会不会增加课程供学生选择,都没有关系.那么我们要呈现给用户看的报表一般是这样的: 图2  可以看到存储数据的时候采用的是列式存储,最终呈

  • 浅析SQL语句行列转换的两种方法 case...when与pivot函数的应用

    /*创建数据库*/ CREATE DATABASE tmp go USE tmp go /*创建数据库测试表*/ CREATE TABLE [Scores]     (       [ID] INT IDENTITY(1, 1)                PRIMARY KEY ,       [Student] VARCHAR(20) ,       [Subject] VARCHAR(30) ,       [Score] FLOAT     ) go TRUNCATE TABLE Sc

  • 将MySQL从MyISAM转换成InnoDB错误和解决办法

    后来在做WordPress,一开始还不知道原来WordPress用的是InnoDB数据引擎,于是在原来的数据库里面就建了一个数据库,一开始也没发觉问题,安装,导入sql,都没问题,当时也没多想.直到这几天因为又要装多一个WordPress,用phpmyadmin访问数据库多一点,问题来了.老是一访问WordPress的数据库,就弹出错误提示"Mysqld-nt.exe应用程序错误:"0x005346c4"指令引用的"0x00786000"内存,该内存不能为

随机推荐