行转列方法,decode()与wmsys.wm_concat()

发布时间:2016-12-9 14:04:56 编辑:www.fx114.net 分享查询网我要评论
本篇文章主要介绍了"行转列方法,decode()与wmsys.wm_concat()",主要涉及到行转列方法,decode()与wmsys.wm_concat()方面的内容,对于行转列方法,decode()与wmsys.wm_concat()感兴趣的同学可以参考一下。

SQL>select * from test_tb_grade t;   ID USER_NAME COURSE SCORE 1 1 a ch 90 2 2 a math 98 3 3 a en 95 4 4 b en 99 5 5 b math 92 6 6 b ch 94 7 7 c ch 91 8 8 c math 90 9 9 c en   需求:按学生名分组,查询各科成绩。 一、使用decode()函数: 第一步:将各科转换成列,并显示对应分数,如果没有的则为空,如下表: SQL>SELECT T.USER_NAME,        DECODE(T.COURSE, 'ch', NVL(SCORE, 0)) AS CHINESE,        DECODE(T.COURSE, 'math', SCORE, NULL) AS MATH,        DECODE(T.COURSE, 'en', NVL(SCORE, 0)) AS ENGLISH   FROM TEST_TB_GRADE T; 使用NVL()函数是考虑到表中有值为NULL。   USER_NAME CHINESE MATH ENGLISH 1 a 90     2 a   98   3 a     95 4 b     99 5 b   92   6 b 94     7 c 91     8 c   90   9 c     0 第二步:合并结果并分组 SQL>SELECT T.USER_NAME,        MAX(DECODE(T.COURSE, 'ch', SCORE, NULL)) AS CHINESE,        MAX(DECODE(T.COURSE, 'math', SCORE, NULL)) AS MATH,        MAX(DECODE(T.COURSE, 'en', NVL(SCORE, 0))) AS ENGLISH   FROM TEST_TB_GRADE T  GROUP BY T.USER_NAME  ORDER BY T.USER_NAME ;   USER_NAME CHINESE MATH ENGLISH 1 a 90 98 95 2 b 94 92 99 3 c 91 90 0 二、使用wmsys.wm_concat()函数 1、直接使用wmsys.wm_concat()函数合并(满足行转列分组要求,但结果为乱序) SQL>SELECT USER_NAME,        WMSYS.WM_CONCAT(T.COURSE) COURSE,        WMSYS.WM_CONCAT(T.SCORE) SCORE   FROM TEST_TB_GRADE T  GROUP BY T.USER_NAME ;   USER_NAME COURSE SCORE 1 a ch,math,en 90,95,98 2 b en,math,ch 99,94,92 3 c ch,math,en 91,90 将结果与之前使用decode()函数的相比,可以发现现在的结果中合并列COURSE、SCORE中的值呈乱序排列。 2、合并时排序(满足行转列分组要求,结果已排序) 第一步:使用分析函数按USER_NAME 分组后以COURSE排序。如下表: SQL>SELECT T.USER_NAME,        WMSYS.WM_CONCAT(COURSE) OVER(PARTITION BY USER_NAME ORDER BY COURSE) COURSE,        WMSYS.WM_CONCAT(NVL(SCORE, 0)) OVER(PARTITION BY USER_NAME ORDER BY COURSE) SCORE   FROM TEST_TB_GRADE T;   USER_NAME COURSE SCORE 1 a ch 90 2 a ch,en 90,95 3 a ch,en,math 90,95,98 4 b ch 94 5 b ch,en 94,99 6 b ch,en,math 94,99,92 7 c ch 91 8 c ch,en 91,0 9 c ch,en,math 91,0,90 第二步:建一伪列TOP来给每个分组中排序列号,取出其中TOP为1的数据即为需求结果,如下表: SQL>SELECT *   FROM (SELECT T.USER_NAME,                WMSYS.WM_CONCAT(COURSE) OVER(PARTITION BY USER_NAME ORDER BY COURSE) COURSE,                WMSYS.WM_CONCAT(NVL(SCORE, 0)) OVER(PARTITION BY USER_NAME ORDER BY COURSE) SCORE,                ROW_NUMBER() OVER(PARTITION BY USER_NAME ORDER BY COURSE DESC) TOP           FROM TEST_TB_GRADE T)  WHERE TOP = 1 ;   USER_NAME COURSE SCORE TOP 1 a ch,en,math 90,95,98 1 2 b ch,en,math 94,99,92 1 3 c ch,en,math 91,0,90 1 总结:由上面的例子中可以知道,decode()方法的行转列需要确定列中的值,在已知列值的情况下使用decode()可以获得更好的展现效果。而wmsys.wm_concat()更为灵活。 ----------------------------------------------------------------------------------------------------------------------------------- 对wmsys.wm_concat()函数的补充 1、行转列后变成一列,显示格式为“学科(分数)”,并且按学科名排序 SQL>SELECT user_name,r   FROM (SELECT USER_NAME,                WMSYS.WM_CONCAT(T.COURSE || '(' || T.SCORE || ')') OVER(PARTITION BY T.USER_NAME ORDER BY T.COURSE) r,                ROW_NUMBER() OVER(PARTITION BY T.USER_NAME ORDER BY T.COURSE DESC) B           FROM TEST_TB_GRADE T)  WHERE B = 1 ;   USER_NAME R 1 a ch(90),en(95),math(98) 2 b ch(94),en(99),math(92) 3 c ch(91),en(),math(90) 这段SQL其实是上段SQL的简单修改,将两个字段合并后放在wmsys.wm_concat()函数,然后同样是使用分析函数排序,同样是建一伪列来过滤。 2、不使用row_number()伪列来过滤数据 1)行转列,分两列,并排序 SQL>SELECT USER_NAME, MAX(COURSE), MAX(SCORE)   FROM (SELECT T.USER_NAME,                WMSYS.WM_CONCAT(T.COURSE) OVER(PARTITION BY T.USER_NAME ORDER BY T.COURSE) COURSE,                WMSYS.WM_CONCAT(NVL(T.SCORE, 0)) OVER(PARTITION BY T.USER_NAME ORDER BY T.COURSE) SCORE           FROM TEST_TB_GRADE T)  GROUP BY USER_NAME; 子查询里与上面的SQL一样,但是去掉了row_number(),而使用group by分组与max()函数。与使用decode()函数中max()一样。 2)行转列,合一列,并排序 SQL>SELECT USER_NAME, MAX(A)   FROM (SELECT T.USER_NAME,                WMSYS.WM_CONCAT(COURSE || '(' || SCORE || ')') OVER(PARTITION BY USER_NAME ORDER BY COURSE) A           FROM TEST_TB_GRADE T)  GROUP BY USER_NAME; 同理。

上一篇:oracle--sql内部处理机制
下一篇:在shell脚本中调用另一个脚本的三种不同方法(fork, exec, source)

相关文章

相关评论