SQL>select * from test_tb_grade t;
第二步:合并结果并分组
将结果与之前使用decode()函数的相比,可以发现现在的结果中合并列COURSE、SCORE中的值呈乱序排列。
这段SQL其实是上段SQL的简单修改,将两个字段合并后放在wmsys.wm_concat()函数,然后同样是使用分析函数排序,同样是建一伪列来过滤。
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 |
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) |
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;
同理。