ORACLE--SQL日常问题和技巧1（列变行、自定义查询结构、将字符串分割为多条记录）

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)

1 SELECT
2     TMP1.COURSE ASCOURSE,
3     NVL ( TMP2.SCORE, 0 ) ASSCORE 
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 ASCOURSE,
37 SCORE 
38         FROM
39 LGQ_TEST 
40     ) TMP2 ON TMP1.COURSE = TMP2.COURSE

1 SELECT
2     REGEXP_SUBSTR( '赵-钱-孙-李', '[^-]+', 1, ROWNUM ) 
3 FROM
4     dual CONNECT BY ROWNUM <= LENGTH( '赵-钱-孙-李' ) -LENGTH(
5     regexp_replace( '赵-钱-孙-李', '-', '' )) + 1