文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

sql行转列、列转行的方法

2024-04-02 19:55

关注

  sql行转列、列转行的方法

如题:有一张表EMP,里面有两个字段:name,chengji  有三条记录,分别表示语文(name) 70分,数学(name) 80分,英语(name) 58分,请用一条sql查询出这三条记录并以条件显示出来,大于等于80表示优秀,大于等于60表示及格,小于60分表示不及格!要求显示格式如上!

首先我们创建表,添加如题数据!

CREATE TABLE emp(NAME VARCHAR(20),chengji INT);

INSERT INTO emp VALUES('语文',70),('数学',80),('英语',58);

sql行转列、列转行的方法

根据题目要求,我们需要将这三行的结果做判断,然后以列的形式显示,这其中有一个行转列的操作。


第一种sql写法:


SELECT MAX(CASE WHEN  NAME='语文' THEN (CASE WHEN chengji>=80  THEN '优秀' WHEN   chengji<80 AND chengji>=60 THEN '及格' ELSE  '不及格' END)  ELSE '' END) '语文'  ,

MAX(CASE WHEN  NAME='数学' THEN (CASE WHEN chengji>=80  THEN '优秀' WHEN   chengji<80 AND chengji>=60 THEN '及格' ELSE  '不及格' END)  ELSE '' END)  '数学',

MAX(CASE WHEN  NAME='英语' THEN (CASE WHEN chengji>=80  THEN '优秀' WHEN   chengji<80 AND chengji>=60 THEN '及格' ELSE  '不及格' END) ELSE '' END ) '英语' FROM  emp

执行结果如下:

sql行转列、列转行的方法

  备注:上述sql中使用了max(case)这种用法,max这里的主要作用是为了在3次判断中,取到不为空字符串''的,语文,数学,英语!


 第二种写法采用group_concat函数也是可以拼接出如图所有要的结果写法如下:


SELECT GROUP_CONCAT(NAME SEPARATOR '|')  FROM   emp  UNION ALL   SELECT   

GROUP_CONCAT(CASE WHEN chengji>=80  THEN '优秀' WHEN   chengji<80 AND chengji>=60 THEN '及格' ELSE  '不及格' END   SEPARATOR '|' ) FROM  emp

执行结果如下:

sql行转列、列转行的方法

这样看,这个结果也还是可以接受, 这里采用了group_concat函数,将列的类容连接起来,作为行!不过这样的结果有点生硬的感觉!


补充一点:这里的sql写法我们可以看出,如果想要通过第一种写法。我们前面必须要知道列的内容如语文,数学,英语,但是第二种我们却不用知道! 这里我们想到了一种方法,通过存储过程,将想要的第一种方法的sql拼出来,然后执行这样的话,后面如果我们的表再添加列,或者减少列,也不会报错!

写法如下:

DELIMITER $$


USE `yhtest`$$


DROP PROCEDURE IF EXISTS `yhtest`$$


CREATE DEFINER=`root`@`%` PROCEDURE `yhtest`()

BEGIN

SET @sql = NULL;

SELECT

  GROUP_CONCAT(DISTINCT

    CONCAT(

       'MAX(CASE WHEN  NAME=','\'',emp.name,'\'','THEN (CASE WHEN chengji>=80  THEN ', '\'' ,'优秀','\'' ,' WHEN  

        chengji<80 AND chengji>=60 THEN ', '\'' ,'及格' ,'\'' ,' ELSE ', '\'' ,'不及格' ,'\'' ,' END)  ELSE ', '\'','\'',' END) ','\'',emp.name,'\'' 

    )

  )

INTO @sql

FROM emp ;

SET @sql = CONCAT('select  ',@sql, ' from  emp');

PREPARE stmt1 FROM @sql;

EXECUTE stmt1;

DEALLOCATE PREPARE stmt1;

  END$$


DELIMITER ;

调用一下: call yhtest();

sql行转列、列转行的方法

插入几条数据!我们假设提前不知道有多少个科目!

INSERT INTO emp VALUES('物理',72),('体育',84);

sql行转列、列转行的方法

这里有个问题!由于我们在存储过程中使用了group_concat函数,这个拼接函数最大拼接长度为1024(默认) 超过固定长度,截断处理! 由数据库参数group_concat_max_len 控制!我们可以根据需要认为调整!


group_concat  调整拼接符号 group_concat(name  separator  '_')

group_concat  排序:group_concat(name order by name  separator  '_')



 

阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯