前言:
由于数据库设计原因,需要对数据进行行转列操作,列的情况比较多,不唯一,所以需要动态创建行转列。
环境:
Mysql 5.7数据库
内容讲解
今天的工作主要分为两个阶段,首先要熟悉静态行转列操作原因,然后带你熟悉动态行转列操作原理。
- 表结构设计
说明:测试使用表我们使用简单的用户和课程表结构,方便大家熟悉下行转列的原理。
表结构
表中数据
- 静态行转列
查询SQL:
- select id,name
- ,sum(case course when '数学' then value end) as 数学
- ,sum(case course when '英语' then value end) as 物理
- ,sum(case course when '语文' then value end) as 语文from user
- GROUP BY id,name
查询结果:
实现原理:由上图我们已经实现的SQL可以看出,首先获取的是姓名,然后根据课程的不同值进行判断,并获取数据值,最后重命名。
案例:我们简单选择张三的数据,来看SQL是如何运作的。
首先 :select id,name是获取的张三姓名
然后 :case course when '数学' then value end ,然后使用case when 判断课程,如果是数学,则取那条数据库的value值,并重命名为数学
接着:判断张三的课程是否有英语,如果有则把对应的value值赋值过去。
最后:这样就形成了有 id,name,数学,物理,语文 等5列,并有对应的值。
思考:这样的表结构,如果张三不只是有数学和物理等科目,假设还有化学、体育等等数据,这样手动书写SQL就很复杂。反问一下,是不是张三有多少个课程写多少个case when 获取对应的科目和值就可以了呢?
- 动态行转列
思路:获取所有的课程列,然后拼接到case when
接下来拼接到case when 即可,我看很多的实现需要创建函数,然而我不想创建函数,所有我在想有什么好的方式进行操作呢。
在这里给大家介绍下,网友的一些做法。该方式是获取所有的列,然后循环拼接case when 。