MySQL 动态 行转列（列值转换列名）

最新推荐文章于 2024-03-15 13:54:53 发布
最新推荐文章于 2024-03-15 13:54:53 发布
阅读量2.8k 收藏 13
点赞数 2
分类专栏： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/NieJiYu/article/details/88757544
版权 
数据表结构

三张表：学生表、课程表、成绩表

 

 

  1. CREATE TABLE `student` (

  2. `stuid` VARCHAR(16) NOT NULL COMMENT '学号',

  3. `stunm` VARCHAR(20) NOT NULL COMMENT '学生姓名',

  4. PRIMARY KEY (`stuid`)

  5. )

  6. COLLATE='utf8_general_ci'

  7. ENGINE=InnoDB;

 

  1. CREATE TABLE `courses` (

  2. `courseno` VARCHAR(20) NOT NULL,

  3. `coursenm` VARCHAR(100) NOT NULL,

  4. PRIMARY KEY (`courseno`)

  5. )

  6. COMMENT='课程表'

  7. COLLATE='utf8_general_ci'

  8. ENGINE=InnoDB;

  • 成绩表

 

  1. CREATE TABLE `score` (

  2. `stuid` VARCHAR(16) NOT NULL,

  3. `courseno` VARCHAR(20) NOT NULL,

  4. `scores` FLOAT NULL DEFAULT NULL,

  5. PRIMARY KEY (`stuid`, `courseno`)

  6. )

  7. COLLATE='utf8_general_ci'

  8. ENGINE=InnoDB;

以上就是数据库表的结构，没有建立外键，但是可以看到成绩表中关联关系

知识点： innodb和myisam的区别

 

InnoDB和MyISAM是许多人在使用MySQL时最常用的两个表类型，这两个表类型各有优劣，视具体应用而定。基本的差别为：MyISAM类型不支持事务处理等高级处理，而InnoDB类型支持。MyISAM类型的表强调的是性能，其执行数度比InnoDB类型更快，但是不提供事务支持，而InnoDB提供事务支持已经外部键等高级数据库功能。

    以下是一些细节和具体实现的差别：
    ◆1.InnoDB不支持FULLTEXT类型的索引。
    ◆2.InnoDB 中不保存表的具体行数，也就是说，执行select count(*) from table时，InnoDB要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count(*)语句包含 where条件时，两种表的操作是一样的。
    ◆3.对于AUTO_INCREMENT类型的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中，可以和其他字段一起建立联合索引。
    ◆4.DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除。
    ◆5.LOAD TABLE FROM MASTER操作对InnoDB是不起作用的，解决方法是首先把InnoDB表改成MyISAM表，导入数据后再改成InnoDB表，但是对于使用的额外的InnoDB特性(例如外键)的表不适用。
    另外，InnoDB表的行锁也不是绝对的，假如在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表，例如update table set num=1 where name like “%aaa%”
    两种类型最主要的差别就是Innodb 支持事务处理与外键和行级锁.而MyISAM不支持.所以MyISAM往往就容易被人认为只适合在小项目中使用。

数据准备

 

  1. /*学生表数据*/

  2. Insert Into student (stuid, stunm) Values('1001', '张三');

  3. Insert Into student (stuid, stunm) Values('1002', '李四');

  4. Insert Into student (stuid, stunm) Values('1003', '赵二');

  5. Insert Into student (stuid, stunm) Values('1004', '王五');

  6. Insert Into student (stuid, stunm) Values('1005', '刘青');

  7. Insert Into student (stuid, stunm) Values('1006', '周明');

 

  1. /*课程表数据*/

  2. Insert Into courses (courseno, coursenm) Values('C001', '大学语文');

  3. Insert Into courses (courseno, coursenm) Values('C002', '新视野英语');

  4. Insert Into courses (courseno, coursenm) Values('C003', '离散数学');

  5. Insert Into courses (courseno, coursenm) Values('C004', '概率论与数理统计');

  6. Insert Into courses (courseno, coursenm) Values('C005', '线性代数');

  7. Insert Into courses (courseno, coursenm) Values('C006', '高等数学(一)');

  8. Insert Into courses (courseno, coursenm) Values('C007', '高等数学(二)');

 

  1. /*成绩表数据*/

  2. Insert Into score(stuid, courseno, scores) Values('1001', 'C001', 67);

  3. Insert Into score(stuid, courseno, scores) Values('1002', 'C001', 68);

  4. Insert Into score(stuid, courseno, scores) Values('1003', 'C001', 69);

  5. Insert Into score(stuid, courseno, scores) Values('1004', 'C001', 70);

  6. Insert Into score(stuid, courseno, scores) Values('1005', 'C001', 71);

  7. Insert Into score(stuid, courseno, scores) Values('1006', 'C001', 72);

  8. Insert Into score(stuid, courseno, scores) Values('1001', 'C002', 87);

  9. Insert Into score(stuid, courseno, scores) Values('1002', 'C002', 88);

  10. Insert Into score(stuid, courseno, scores) Values('1003', 'C002', 89);

  11. Insert Into score(stuid, courseno, scores) Values('1004', 'C002', 90);

  12. Insert Into score(stuid, courseno, scores) Values('1005', 'C002', 91);

  13. Insert Into score(stuid, courseno, scores) Values('1006', 'C002', 92);

  14. Insert Into score(stuid, courseno, scores) Values('1001', 'C003', 83);

  15. Insert Into score(stuid, courseno, scores) Values('1002', 'C003', 84);

  16. Insert Into score(stuid, courseno, scores) Values('1003', 'C003', 85);

  17. Insert Into score(stuid, courseno, scores) Values('1004', 'C003', 86);

  18. Insert Into score(stuid, courseno, scores) Values('1005', 'C003', 87);

  19. Insert Into score(stuid, courseno, scores) Values('1006', 'C003', 88);

  20. Insert Into score(stuid, courseno, scores) Values('1001', 'C004', 88);

  21. Insert Into score(stuid, courseno, scores) Values('1002', 'C004', 89);

  22. Insert Into score(stuid, courseno, scores) Values('1003', 'C004', 90);

  23. Insert Into score(stuid, courseno, scores) Values('1004', 'C004', 91);

  24. Insert Into score(stuid, courseno, scores) Values('1005', 'C004', 92);

  25. Insert Into score(stuid, courseno, scores) Values('1006', 'C004', 93);

  26. Insert Into score(stuid, courseno, scores) Values('1001', 'C005', 77);

  27. Insert Into score(stuid, courseno, scores) Values('1002', 'C005', 78);

  28. Insert Into score(stuid, courseno, scores) Values('1003', 'C005', 79);

  29. Insert Into score(stuid, courseno, scores) Values('1004', 'C005', 80);

  30. Insert Into score(stuid, courseno, scores) Values('1005', 'C005', 81);

  31. Insert Into score(stuid, courseno, scores) Values('1006', 'C005', 82);

  32. Insert Into score(stuid, courseno, scores) Values('1001', 'C006', 77);

  33. Insert Into score(stuid, courseno, scores) Values('1002', 'C006', 78);

  34. Insert Into score(stuid, courseno, scores) Values('1003', 'C006', 79);

  35. Insert Into score(stuid, courseno, scores) Values('1004', 'C006', 80);

  36. Insert Into score(stuid, courseno, scores) Values('1005', 'C006', 81);

  37. Insert Into score(stuid, courseno, scores) Values('1006', 'C006', 82);

为什么要行转列

上面是进行成绩查询的时候看到的纵列的结果，但是我们想要的是下面的结果

那么需要这样的结果就要进行行转列来操作了。

静态行转列

 

  1. Select st.stuid, st.stunm,

  2. MAX(CASE c.coursenm WHEN '大学语文' THEN s.scores ELSE 0 END ) '大学语文',

  3. MAX(CASE c.coursenm WHEN '新视野英语' THEN ifnull(s.scores,0) ELSE 0 END ) '新视野英语',

  4. MAX(CASE c.coursenm WHEN '离散数学' THEN ifnull(s.scores,0) ELSE 0 END ) '离散数学',

  5. MAX(CASE c.coursenm WHEN '概率论与数理统计' THEN ifnull(s.scores,0) ELSE 0 END ) '概率论与数理统计',

  6. MAX(CASE c.coursenm WHEN '线性代数' THEN ifnull(s.scores,0) ELSE 0 END ) '线性代数',

  7. MAX(CASE c.coursenm WHEN '高等数学(一)' THEN ifnull(s.scores,0) ELSE 0 END ) '高等数学(一)',

  8. MAX(CASE c.coursenm WHEN '高等数学(二)' THEN ifnull(s.scores,0) ELSE 0 END ) '高等数学(二)'

  9. From Student st

  10. Left Join score s On st.stuid = s.stuid

  11. Left Join courses c On c.courseno = s.courseno

  12. Group by st.stuid

看上面的语句可以看出，我们是在知道固定的几门课程之后，可以使用

MAX(CASE c.coursenm WHEN '线性代数' THEN ifnull(s.scores,0) ELSE 0 END ) '线性代数',

这样的语句来实现行转列

但我们都知道，课程不仅仅这几门，如果用上面的语句去写，第一要确定有多少课程，这么多课程的课程名要再拿出来，那样的话写一个查询语句下来，可是要写很多了。那么就想能不能动态进行行转列的操作？答案当然是肯定的了！

动态行转列

那么如何进行动态行转列呢？

首先我们要动态获取这样的语句

 

  1. MAX(CASE c.coursenm WHEN '大学语文' THEN s.scores ELSE 0 END ) '大学语文',

  2. MAX(CASE c.coursenm WHEN '线性代数' THEN ifnull(s.scores,0) ELSE 0 END ) '线性代数',

  3. MAX(CASE c.coursenm WHEN '离散数学' THEN ifnull(s.scores,0) ELSE 0 END ) '离散数学'

而不是像上面那样一句句写出来，那如何得到这样的语句呢？

这里就要用到SQL语句拼接了。具体就是下面的语句

 

  1. SELECT

  2. GROUP_CONCAT(DISTINCT

  3. CONCAT(

  4. 'MAX(IF(c.coursenm = ''',

  5. c.coursenm,

  6. ''', s.scores, 0)) AS ''',

  7. c.coursenm, ''''

  8. )

  9. )

  10. FROM courses c;

得到的结果就是

 

  1. MAX(IF(c.coursenm = '大学语文', s.scores, 0)) AS '大学语文',

  2. MAX(IF(c.coursenm = '新视野英语', s.scores, 0)) AS '新视野英语',

  3. MAX(IF(c.coursenm = '离散数学', s.scores, 0)) AS '离散数学',

  4. MAX(IF(c.coursenm = '概率论与数理统计', s.scores, 0)) AS '概率论与数理统计',

  5. MAX(IF(c.coursenm = '线性代数', s.scores, 0)) AS '线性代数',

  6. MAX(IF(c.coursenm = '高等数学(一)', s.scores, 0)) AS '高等数学(一)',

  7. MAX(IF(c.coursenm = '高等数学(二)', s.scores, 0)) AS '高等数学(二)'

对，没错，就是我们上面进行行转列查询要用的语句，那样就不用知道多少课程和这些课程的名字，只要这样几行代码便可以得到动态的列了。

group_concat()，手册上说明:该函数返回带有来自一个组的连接的非NULL值的字符串结果。
通俗点理解，其实是这样的：group_concat()会计算哪些行属于同一组，将属于同一组的列显示出来。要返回哪些列，由函数参数(就是字段名)决定。分组必须有个标准，就是根据group by指定的列进行分组。

concat() 操作的参数是数组，那么添加的是数组中的元素，而不是数组。该方法不会改变现有的数组，而仅仅会返回被连接数组的一个副本

动态的列是拿到了，那如何再结合SQL语句进行查询得到结果呢？
这里要说明一点，因为用到了拼接函数，如果像上面的查询语句，只是把那几行语句替换掉，也就是下面这样

 

  1. Select st.stuid, st.stunm,

  2. (

  3. SELECT

  4. GROUP_CONCAT(DISTINCT

  5. CONCAT(

  6. 'MAX(IF(c.coursenm = ''',

  7. c.coursenm,

  8. ''', s.scores, NULL)) AS ',

  9. c.coursenm

  10. )

  11. )

  12. FROM courses c

  13. )

  14. From Student st

  15. Left Join score s On st.stuid = s.stuid

  16. Left Join courses c On c.courseno = s.courseno

  17. Group by st.stuid;

然而得到的结果却是这样的

这里我就不多做赘述了，想必大家也明白。那么既然这样不行，那该怎么做呢？

没错，这里就要像普通的那些语句那样，进行声明，将语句拼接完整之后，再执行，也就是下面这样

 

  1. SET @sql = NULL;

  2. SELECT

  3. GROUP_CONCAT(DISTINCT

  4. CONCAT(

  5. 'MAX(IF(c.coursenm = ''',

  6. c.coursenm,

  7. ''', s.scores, 0)) AS ''',

  8. c.coursenm, ''''

  9. )

  10. ) INTO @sql

  11. FROM courses c;

  12.  

  13. SET @sql = CONCAT('Select st.stuid, st.stunm, ', @sql,

  14. ' From Student st

  15. Left Join score s On st.stuid = s.stuid

  16. Left Join courses c On c.courseno = s.courseno

  17. Group by st.stuid');

  18. PREPARE stmt FROM @sql;

  19. EXECUTE stmt;

  20. DEALLOCATE PREPARE stmt;

直接执行这些语句，得到如下结果。

MySQL prepare语法： 
PREPARE statement_name FROM preparable_SQL_statement; /*定义*/ 
EXECUTE statement_name [USING @var_name [, @var_name] ...]; /*执行预处理语句*/ 
{DEALLOCATE | DROP} PREPARE statement_name /*删除定义*/ ; 

详解见：http://www.cnblogs.com/gklsy/archive/2012/01/10/2317934.html

当然这个语句拼接中的查询可以加入条件查询，比如我们要查询学号是1003的成绩
也就是下面这样

语句则如下

 

  1. SET @sql = NULL;

  2. SET @stuid = '1003';

  3. SELECT

  4. GROUP_CONCAT(DISTINCT

  5. CONCAT(

  6. 'MAX(IF(c.coursenm = ''',

  7. c.coursenm,

  8. ''', s.scores, 0)) AS ''',

  9. c.coursenm, ''''

  10. )

  11. ) INTO @sql

  12. FROM courses c;

  13.  

  14. SET @sql = CONCAT('Select st.stuid, st.stunm, ', @sql,

  15. ' From Student st

  16. Left Join score s On st.stuid = s.stuid

  17. Left Join courses c On c.courseno = s.courseno

  18. Where st.stuid = ''', @stuid, '''

  19. Group by st.stuid');

  20. PREPARE stmt FROM @sql;

  21. EXECUTE stmt;

  22. DEALLOCATE PREPARE stmt;

对比前面的语句，我们可以看到在第二行的Left join后面我改了一些，还有就是前面的变量加了一个@stuid [ 注：这里的 @ 符号是在SQL语句定义变量习惯用法，我个人理解应该是用来区分吧！]

那么问题来了，行转列的查询已经实现了，怎么标题中还写着存储过程？对，没错，就是存储过程！

储存过程：http://blog.sina.com.cn/s/blog_52d20fbf0100ofd5.html      http://www.cnblogs.com/exmyth/p/3303470.html

像上面的语句，我们如果直接在MySQL中操作是没问题的，但如果用到项目中，那么这个语句显然我们没法用，而且我这次做的项目是结合使用MyBatis，大家都知道在MyBatis中的XML文件中可以自己写SQL语句，但是这样的很显然我们没法放到XML文件中。

而且最关键的是，这里不能用 If 条件，好比我们要判断学号是否为空或者等于0再加上条件进行查询，可是这里不支持。
没错就是下面这样

 

  1. SET @sql = NULL;

  2. SET @stuid = '1003';

  3. SET @courseno = 'C002';

  4.  

  5. SELECT

  6. GROUP_CONCAT(DISTINCT

  7. CONCAT(

  8. 'MAX(IF(c.coursenm = ''',

  9. c.coursenm,

  10. ''', s.scores, 0)) AS ''',

  11. c.coursenm, ''''

  12. )

  13. ) INTO @sql

  14. FROM courses c;

  15.  

  16. SET @sql = CONCAT('Select st.stuid, st.stunm, ', @sql,

  17. ' From Student st

  18. Left Join score s On st.stuid = s.stuid

  19. Left Join courses c On c.courseno = s.courseno');

  20.  

  21. IF @stuid is not null and @stuid != 0 then

  22. SET @sql = CONCAT(@sql, ' Where st.stuid = ''', @stuid, '''');

  23. END IF;

  24.  

  25. SET @sql = CONCAT(@sql, ' Group by st.stuid');

  26.  

  27. PREPARE stmt FROM @sql;

  28. EXECUTE stmt;

  29. DEALLOCATE PREPARE stmt;

对，我就是加上 if 之后人家就是不支持，就是这么任性。

所以就要用到存储过程啦，而且用存储过程的好处是，方便我们调用，相当于一个函数，其他可能也是类似的查询不需再重复写代码，直接调存储过程就好，还能随心所欲的加上if条件判断，多么美好的事情，哈哈~。

那么说到存储过程，这里该如何写呢？
创建存储过程的语句我就不多写了，这里呢把上面的查询语句直接放到创建存储过程的begin和end直接就可以了，如下：

 

  1. DELIMITER &&

  2. drop procedure if exists SP_QueryData;

  3. Create Procedure SP_QueryData(IN stuid varchar(16))

  4. READS SQL DATA

  5. BEGIN

  6.  

  7. SET @sql = NULL;

  8. SET @stuid = NULL;

  9. SELECT

  10. GROUP_CONCAT(DISTINCT

  11. CONCAT(

  12. 'MAX(IF(c.coursenm = ''',

  13. c.coursenm,

  14. ''', s.scores, 0)) AS ''',

  15. c.coursenm, '\''

  16. )

  17. ) INTO @sql

  18. FROM courses c;

  19.  

  20. SET @sql = CONCAT('Select st.stuid, st.stunm, ', @sql,

  21. ' From Student st

  22. Left Join score s On st.stuid = s.stuid

  23. Left Join courses c On c.courseno = s.courseno');

  24.  

  25. IF stuid is not null and stuid <> '' then

  26. SET @stuid = stuid;

  27. SET @sql = CONCAT(@sql, ' Where st.stuid = \'', @stuid, '\'');

  28. END IF;

  29.  

  30. SET @sql = CONCAT(@sql, ' Group by st.stuid');

  31.  

  32. PREPARE stmt FROM @sql;

  33. EXECUTE stmt;

  34. DEALLOCATE PREPARE stmt;

  35.  

  36. END &&

  37.  

  38. DELIMITER ;

嗯，对比上面简单的SQL语句可以看出，这里使用了 if 语句，对学号进行了判断

不过这里要注意一点，这里的if语句不像我们平时Java啊那种写法也就是下面

 

  1. if(条件)

  2. {

  3. 要执行的语句块

  4. }

对，在SQL里面的if语句不一样，不需要括号啊什么的，就像直接说英文一样

 

  1. IF @stuid is not null and @stuid != 0 then

  2. SET @sql = CONCAT(@sql, ' Where st.stuid = ''', @stuid, '''');

  3. END IF;

嗯，就是这么简单明了，如果条件满足，那么就怎么样，然后结束。

然后我们就可以传参数调用这个SP了

CALL `SP_QueryData`('1001');

得到如下结果

当然我们也可以直接传个空串过去

CALL `SP_QueryData`('');

同样得到我们想要的结果

好了，以上就是这次我在mysql进行动态行转列的实现过程。

总结及问题

开始的时候，只想到要行转列，写着写着突然发现要动态的，因为我不确定到底有多少列。
在网上各种找资料，然而看不太懂！

后来，参考了Pivot table with dynamic columns in MySQL这个，才写出来的。

然后是各种问题，先是SQL语句中加入if条件，我像平时写java那样，发现并没有什么用，网上也说就是这种

 

  1. IF(stuid is not null && stuid <> '') then

  2. SET @stuid = stuid;

  3. SET @sql = CONCAT(@sql, ' Where st.stuid = \'', @stuid, '\'');

  4. END IF;

可是我这么写了之后并没有什么用，还是报错，找了不少之后才发现原来不是这么写的，然后改了过来。

改完之后我以为可以了，可是，发现依旧不行。然后我就在想是不是这里不能用if判断，因为不是一个function或者procedure，于是我就写创建procedure的语句。

改造完之后，procedure成功的创建了。那创建完我就试试能不能，调用procedure之后，当当当当，结果出来了。

嗯，这个过程还是收获很多的，对MySQL的行转列，以及存储过程，还有在SQL语句中的使用不一样的地方等。
而且，这个行转列的实现了之后，这个项目基本上没啥大问题了对数据的处理，相当好啊，哈哈~

以上就是我在行转列实现的过程中所有的内容，相对来说，我觉得，这里写的很清楚很明了了，所以只要你有耐心看完并认真研究的话，这个内容对你的行转列还是有很大裨益的。

NieJiYu
关注
  • 2
    点赞
  • 踩
  • 13
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mysql动态行转列
11-04
mysql动态行转列的例子
db2struct:将mysql表转换为golang结构
05-04
通过从数据库中读取有关列结构的详细信息，db2struct生成具有所需列名，数据类型和注释的go兼容结构类型。 生成的数据类型包括对可为空的列或支持，以及所期望的基本内置go类型。 Db2Struct是基于ChimeraCoder的...
mysql将某一个月所有天数构造出来
qq_38049901的博客
09-01 269
函数：将指定的日期值添加到现有日期上，并返回结果。写项目时会遇到统计某个月每一天数据的场景。mysql可以将某个月的所有日期构造出来。：返回给定日期的月份的日期部分。：将字符串格式转换成日期格式。：返回某个月最后一天的日期。
Mysql 每月及每月天数
weixin_42672560的博客
04-24 639
mysql 月份，月份天数
mysql中动态行转列
最新发布
Li_789的博客
03-15 146
mysql行转列
mysql每个月的天数_MySQL在一个月内的日期范围内的天数(预订表)
weixin_29945371的博客
01-28 591
我正尝试使用以下信息创建住宿服务报告：Number of Bookings (Easy, use the COUNT function)Revenue Amount (Kind of easy).Number of Room nights. (Rather Hard it seems)分为一年中的每个月。限制 - 我目前使用PHP / MySQL创建此报告。我将数据从预订系统中一次抽出1个月，然后...
mysql按照天数统计最近7天（或者十天，一个月）的报表，如果当天没有数据则填0
ghd602338792的博客
04-30 1886
转载至:https://blog.csdn.net/qq15577969/article/details/104155299 需要分析： 按照天数统计每天的数据总和，如果其中有几天没有数据，那么group by 返回会忽略那几天，如何填充为0呢？ 如下图，按照需求打算统计2020年01月25日~2020年02月03日的数据，用于图表展示，但只有2月2日和2月3日有数据，其他的都没有，非常不符合...
MySQL行专列----动态列
qq_37196265的博客
07-13 1446
SET @EE=‘’;/**将CONCAT()拼接后的SQL片段赋值给变量@EE。:= 对变量进行赋值concat() 拼接字符串函数sum() 求和函数IF(condition, value_if_true, value_if_false) 如果条件为 TRUE，IF() 函数返回一个值，如果条件为 FALSE，则返回另一个值。sum(if()) : 如果subject = subject, 则取对应的score， 否则取0进行求和，并取别名为subject字段对应的值。
MySQL 动态 行转列
weixin_44254243的博客
02-24 6211
MySQL 动态 行转列1.需求2.建表3.插入数据4. 转换前结果5. 动态转换 1.需求 在每一行的内容不确定的情况下，需要动态的把行转为列。 2.建表 DROP TABLE IF EXISTS tb_score; CREATE TABLE tb_score( id INT(11) NOT NULL auto_increment, userid VARCHAR(20) NOT NULL COMMENT '用户id', subject VARCHAR(20) COMMENT '科
mysql动态行转列详解
zhilo1231的博客
07-19 743
2、插入数据 数据少可以多改变userid后多插入一些。1、建立一个学生对应分数的表。4、准备开始写sql语句。3、得到一张有数据的表。
搞定mysql的 行转列（7种方法） 和 列转行
热门推荐
qq_58148854的博客
02-01 1万+
一、行转列1、使用case…when…then 2、使用SUM(IF()) 生成列3、使用SUM(IF()) 生成列 + WITH ROLLUP 生成汇总行4、使用SUM(IF()) 生成列，直接生成汇总结果，不再利用子查询5、使用SUM(IF()) 生成列 + UNION 生成汇总行,并利用 IFNULL将汇总行标题显示为 Total6、动态查询列值不确定的情况7、合并字段显示：group_concat()二、列转行 ————————————————即将原本同一列下多行的不同内容作为多个字段，输出对应内容
Java实体类字段生成工具类-将数据库表列字段转为Java实体类驼峰字段
05-29
只需要复制数据库列名，支持多列复制，执行工具类即可生成对应的Java字段代码。 4、优点：使用代码生成驼峰形式的字段，可以减少出错概率，生成的实体类字段符合Java命名规范，易于阅读和理解。 5、使用示例：将...
mysql数据库的基本操作语法
12-05
在表面后面带括号，括号中写列名，values中写指定列名的值即可。当省略列名就表示插入全部数据， 注意插入值的顺序和列的顺序需要保持一致。 Set方式插入，也可以指定列 insert into temp set id = 7, name = 'jason...
mysql数据库设计规范.docx
03-06
为前缀并以日期为后缀，备份表必须以bak_为前缀并以日期(时间戳)为后缀所有存储相同数据的列名和列类型必须一致（一般作为关联列，如果查询时关联列类型不一致会自动进行数据类型隐式转换，会造成列上的索引失效，...
为什么阿里巴巴禁止使用 count(列名)或 count(常量)来替代 count(*)1
08-03
介绍完了COUNT(*)，接下来看看COUNT(1)，对于，这者到底有没有区别，上的说法众说纷纭。有的说 COUNT(*) 执时会转换成 COUNT(1) ，所
Mysql动态行转列（不使用存储过程）
qq_41840735的博客
01-14 2079
实现思路： 动态拼接执行的SQL语句，先把要执行的语句拼在变量里，之后执行。 拼接的过程就是要动态找出数据里的所有列，然后拼成SQL – 建表语句： DROP TABLE IF EXISTS student_score; CREATE TABLE student_score ( id int(8) NOT NULL, name varchar(20) DEFAULT NULL, project varchar(20) DEFAULT NULL, score int(8) DEFAULT NULL, PRIM
MYSQL查询动态行转列的方法
weixin_43941717的博客
02-23 1582
mysql查询动态行转列数据
MySQL存储过程的动态行转列
weixin_34372728的博客
01-15 1213
MySQL存储过程中使用动态行转列 最近做项目关于数据报表处理，然而数据库存储格式和报表展现形式不同，需要进行一下行转列的操作，在做上一个项目的时候也看了一下，但是后来换了读取方式，也就没深入研究这个问题了。 而这一次必须要直面这个问题了，奈何我是那么的不想看，可项目不能因为这个问题卡在这，只好好好找资料看如何解决这个问题。 开始也在网上...
mysql8 动态行转列
05-14
MySQL 8 中可以使用 `JSON_TABLE` 函数来实现将行转列的操作。 假设有以下表结构： ``` CREATE TABLE scores ( id INT PRIMARY KEY, name VARCHAR(50), subject VARCHAR(50), score INT ); ``` 其中包含学生的姓名、学科和成绩，我们想要把学科作为列名，学生姓名作为行名，成绩作为值，可以使用以下 SQL 语句： ``` SELECT name, IFNULL(`math`, 0) AS math, IFNULL(`english`, 0) AS english, IFNULL(`chinese`, 0) AS chinese FROM (SELECT name, subject, score FROM scores) AS t JOIN JSON_TABLE( (SELECT CONCAT('{"', GROUP_CONCAT(CONCAT_WS('":', subject, score)), '}') AS json_data FROM scores GROUP BY name), '$.*' COLUMNS( `math` INT PATH '$."math"', `english` INT PATH '$."english"', `chinese` INT PATH '$."chinese"' ) ) AS jt ON t.subject = jt.subject AND t.score = jt.score GROUP BY name; ``` 首先，内层的子查询使用 `GROUP_CONCAT` 函数将每个学生的成绩转换为 JSON 格式的字符串。然后，使用 `JSON_TABLE` 函数解析这些 JSON 数据，并将它们转换为列。最后，使用 `IFNULL` 函数将空值替换为 0，并按学生姓名进行分组。 这样就可以将行转列，并将学科作为列名，学生姓名作为行名，成绩作为值。

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值