ORACLE--SQL日常问题和技巧1(列变行、自定义查询结构、将字符串分割为多条记录)
1.ORACLE列变行加合计:
查询结果为
1 SELECT 2 nvl( F, '合计' ) F, 3 sum( S1 ) S1, 4 sum( S2 ) S2, 5 sum( S3 ) S3 6 FROM 7 (SELECT 8 F, 9 sum( decode( S, 'S1', N, NULL ) ) S1, 10 sum( decode( S, 'S2', N, NULL ) ) S2, 11 sum( decode( S, 'S3', N, NULL ) ) S3 12 FROM 13 LQG_TEST 14 GROUP BY 15 F) 16 GROUP BY 17 rollup ( 18 F)
2.自定义查询结构,场景如下:
查询语文,数学,英语,科学四门学科的分数,但是表里只有数学和英语。如图:
查询结果为:
看看SQL吧(nvl函数用于oracle):
1 SELECT 2 TMP1.COURSE AS COURSE, 3 NVL ( TMP2.SCORE, 0 ) AS SCORE 4 FROM 5 ( 6 SELECT 7 '语文' COURSE 8 FROM 9 DUAL UNION ALL 10 SELECT 11 '数学' COURSE 12 FROM 13 DUAL UNION ALL 14 SELECT 15 '英语' COURSE 16 FROM 17 DUAL UNION ALL 18 SELECT 19 '科学' COURSE 20 FROM 21 DUAL 22 ) TMP1 23 LEFT JOIN ( 24 SELECT 25 CASE 26 27 WHEN 28 COURSE = 'CHINESE' THEN 29 '语文' 30 WHEN COURSE = 'MATH' THEN 31 '数学' 32 WHEN COURSE = 'ENGLISH' THEN 33 '英语' 34 WHEN COURSE = 'SCIENCE' THEN 35 '科学' 36 END AS COURSE, 37 SCORE 38 FROM 39 LGQ_TEST 40 ) TMP2 ON TMP1.COURSE = TMP2.COURSE
3.将字符串按某字符分割为多条记录
1 SELECT 2 REGEXP_SUBSTR( '赵-钱-孙-李', '[^-]+', 1, ROWNUM ) 3 FROM 4 dual CONNECT BY ROWNUM <= LENGTH( '赵-钱-孙-李' ) - LENGTH( 5 regexp_replace( '赵-钱-孙-李', '-', '' )) + 1
昔日我曾苍老,如今风华正茂(ง •̀_•́)ง