数据库的竖转横(菜鸟小白第一次发)

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/weixin_45326606/article/details/96473811

今天遇到一个题目,数据库的竖转横

首先,有这么一个学生表,然后拥有学号,姓名…一些属性在这里插入图片描述

然后表中有数据若干在这里插入图片描述

要求达到这个效果在这里插入图片描述

经过多方支援,终于


select studentid, name, 
    max(case month  when '1' then money else 0 end)  一月,
    max(case month  when '2' then money else 0 end)  二月,
    sum(money) 总和
  from student 
group by studentid, name

这里使用了聚合函数,所以后面分组使用了group by,而case那个是一个判断,之所以使用max,是因为它并不是有条件的查询,而是全部查询然后在里面判断的,而上面之所以这样写,就是它写了else 0 end,这个意思就是相当于if判断中的else,所以里面并不是单纯只有一月,二月的值,还有0,所以使用了最大值max()函数

展开阅读全文

关于竖表转横表问题!

04-20

我有一个数据表,因为在程序设定初期,用户说明白他的所有要求,导致程序已经完全编制完成,他又来说要一个表,可是这个表就是整个得把以前的表竖转横!rnrn下面把表说明一下,select * from emp_ls where rownum<=10rnrnNAME DH GZDH HPDH AMOUNT YEARSrn---------- ---------- ----- ----- -------------------- ----------rn王枫鑫 7M B5.22 3无 2010rn王枫鑫 7M B5.23 2无 2010rn王枫鑫 7M B5.24 2无 2010rn王枫鑫 7M B5.25 4无 2010rn王枫鑫 7M B5.26 4无 2010rn王枫鑫 7M B5.3 18无 2010rn王枫鑫 7M B5.4 18无 2010rn王枫鑫 7M B5.6 8无 2010rn王枫鑫 7M B5.7 8无 2010rn李树海 7M B4.1 12无 2010rnrn用户要求,把这个表中的hpdh这个转成横的,这个类别有81个大项,就是要一个是rn序号 姓名 B1_1 B1_2…… B5_27rn 1 王枫鑫 null 3无    nullrn 2 李树海 1 null nullrnrn如果hpdh里有的,取值amount,如果没有,就是null。我在网上找了一个,可是不知道为什么就是限制在10条记录以内,可以执行,否则就出错!请各位高手指点!rnselect name,sum(case hpdh when 'B1.1' then amount else null end) B1_1, rn sum(case hpdh when 'B1.2' then amount else null end ) B1_2,rn sum(case hpdh when 'B1.3' then amount else null end ) B1_3,rn sum(case hpdh when 'B1.4' then amount else null end ) B1_4,rn sum(case hpdh when 'B1.5' then amount else null end ) B1_5,rn sum(case hpdh when 'B1.6' then amount else null end ) B1_6,rn sum(case hpdh when 'B1.7' then amount else null end ) B1_7,rn sum(case hpdh when 'B1.8' then amount else null end ) B1_8,rn sum(case hpdh when 'B1.9' then amount else null end ) B1_9,rn sum(case hpdh when 'B1.10' then amount else null end ) B1_10,rn sum(case hpdh when 'B1.11' then amount else null end ) B1_11,rn sum(case hpdh when 'B1.12' then amount else null end ) B1_12,rn sum(case hpdh when 'B1.13' then amount else null end ) B1_13,rn sum(case hpdh when 'B1.14' then amount else null end ) B1_14,rn sum(case hpdh when 'B1.15' then amount else null end ) B1_15,rn sum(case hpdh when 'B1.16' then amount else null end ) B1_16,rn sum(case hpdh when 'B1.17' then amount else null end ) B1_17,rn sum(case hpdh when 'B1.18' then amount else null end ) B1_18,rn sum(case hpdh when 'B1.19' then amount else null end ) B1_19,rn sum(case hpdh when 'B1.20' then amount else null end ) B1_20,rn sum(case hpdh when 'B1.21' then amount else null end ) B1_21,rn sum(case hpdh when 'B1.22' then amount else null end ) B1_22,rn sum(case hpdh when 'B1.23' then amount else null end ) B1_23,rn sum(case hpdh when 'B1.24' then amount else null end ) B1_24,rn sum(case hpdh when 'B1.25' then amount else null end ) B1_25,rn sum(case hpdh when 'B1.26' then amount else null end ) B1_26,rn sum(case hpdh when 'B1.27' then amount else null end ) B1_27,rn sum(case hpdh when 'B1.28' then amount else null end ) B1_28,rn sum(case hpdh when 'B2.1' then amount else null end) B2_1, rn sum(case hpdh when 'B2.2' then amount else null end ) B2_2,rn sum(case hpdh when 'B2.3' then amount else null end ) B2_3,rn sum(case hpdh when 'B2.4' then amount else null end ) B2_4,rn sum(case hpdh when 'B2.5' then amount else null end ) B2_5,rn sum(case hpdh when 'B2.6' then amount else null end ) B2_6,rn sum(case hpdh when 'B2.7' then amount else null end ) B2_7,rn sum(case hpdh when 'B2.8' then amount else null end ) B2_8,rn sum(case hpdh when 'B2.9' then amount else null end ) B2_9,rn sum(case hpdh when 'B2.10' then amount else null end ) B2_10,rn sum(case hpdh when 'B2.11' then amount else null end ) B2_11,rn sum(case hpdh when 'B2.12' then amount else null end ) B2_12,rn sum(case hpdh when 'B2.13' then amount else null end ) B2_13,rn sum(case hpdh when 'B2.14' then amount else null end ) B2_14,rn sum(case hpdh when 'B2.15' then amount else null end ) B2_15,rn sum(case hpdh when 'B2.16' then amount else null end ) B2_16,rn sum(case hpdh when 'B3.1' then amount else null end) B3_1, rn sum(case hpdh when 'B3.2' then amount else null end ) B3_2,rn sum(case hpdh when 'B3.3' then amount else null end ) B3_3,rn sum(case hpdh when 'B3.4' then amount else null end ) B3_4,rn sum(case hpdh when 'B3.5' then amount else null end ) B3_5,rn sum(case hpdh when 'B3.6' then amount else null end ) B3_6,rn sum(case hpdh when 'B4.1' then amount else null end) B4_1, rn sum(case hpdh when 'B4.2' then amount else null end ) B4_2,rn sum(case hpdh when 'B4.3' then amount else null end ) B4_3,rn sum(case hpdh when 'B4.4' then amount else null end ) B4_4,rn sum(case hpdh when 'B4.5' then amount else null end ) B4_5,rn sum(case hpdh when 'B5.1' then amount else null end) B5_1, rn sum(case hpdh when 'B5.2' then amount else null end ) B5_2,rn sum(case hpdh when 'B5.3' then amount else null end ) B5_3,rn sum(case hpdh when 'B5.4' then amount else null end ) B5_4,rn sum(case hpdh when 'B5.5' then amount else null end ) B5_5,rn sum(case hpdh when 'B5.6' then amount else null end ) B5_6,rn sum(case hpdh when 'B5.7' then amount else null end ) B5_7,rn sum(case hpdh when 'B5.8' then amount else null end ) B5_8,rn sum(case hpdh when 'B5.9' then amount else null end ) B5_9,rn sum(case hpdh when 'B5.10' then amount else null end ) B5_10,rn sum(case hpdh when 'B5.11' then amount else null end ) B5_11,rn sum(case hpdh when 'B5.12' then amount else null end ) B5_12,rn sum(case hpdh when 'B5.13' then amount else null end ) B5_13,rn sum(case hpdh when 'B5.14' then amount else null end ) B5_14,rn sum(case hpdh when 'B5.15' then amount else null end ) B5_15,rn sum(case hpdh when 'B5.16' then amount else null end ) B5_16,rn sum(case hpdh when 'B5.17' then amount else null end ) B5_17,rn sum(case hpdh when 'B5.18' then amount else null end ) B5_18,rn sum(case hpdh when 'B5.19' then amount else null end ) B5_19,rn sum(case hpdh when 'B5.20' then amount else null end ) B5_20,rn sum(case hpdh when 'B5.21' then amount else null end ) B5_21,rn sum(case hpdh when 'B5.22' then amount else null end ) B5_22,rn sum(case hpdh when 'B5.23' then amount else null end ) B5_23,rn sum(case hpdh when 'B5.24' then amount else null end ) B5_24,rn sum(case hpdh when 'B5.25' then amount else null end ) B5_25,rn sum(case hpdh when 'B5.26' then amount else null end ) B5_26,rn sum(case hpdh when 'B5.27' then amount else null end ) B5_27rnfrom emp_ls group by namern 论坛

没有更多推荐了,返回首页