今天一个同事问我一个问题,用 SQL 如何实现。
一堆 SQL 运行以后显示如下结果,
A B C
--- -- ----------
AAA A1 123
AAA A2 233
AAA A3 332
BBB B1 288
BBB B2 488
BBB B3 588
BBB B4 688
CCC C1 555
CCC C2 666
。。。
想变成如下结果;
A B C
--- -- ----------
AAA
AAA A1 123
AAA A2 233
AAA A3 332
BBB
BBB B1 288
BBB B2 488
BBB B3 588
BBB B4 688
CCC
CCC C1 555
CCC C2 666
就是 A 列别增加了几个行,B,C列都是空。
刚开始我想到是的 UNION ALL DISTINCT 一个。
方法1如下;
WITH T1 AS (
SELECT 'AAA' A,'A1' B,123 C FROM DUAL UNION ALL
SELECT 'AAA','A2',233 FROM DUAL UNION ALL
SELECT 'AAA','A3',332 FROM DUAL UNION ALL
SELECT 'BBB','B1',288 FROM DUAL UNION ALL
SELECT 'BBB','B2',488 FROM DUAL UNION ALL
SELECT 'BBB','B3',588 FROM DUAL UNION ALL
SELECT 'BBB','B4',688 FROM DUAL UNION ALL
SELECT 'CCC','C1',555 FROM DUAL UNION ALL
SELECT 'CCC','C2',666 FROM DUAL
)
SELECT T1.A , T1.B , T1.C FROM T1
UNION ALL
SELECT DISTINCT T1.A ,NULL,NULL FROM T1
ORDER BY 1,2 NULLS FIRST , 3
;
这个方法很好理解, 但是性能慢。 T1结果很大的时候 DISTINCT 本身就慢。
后来想到了方法2, 性能也挺好, 代码也不多。 缺点是,可能看不懂。
方法2:
WITH T1 AS (
SELECT 'AAA' A,'A1' B,123 C FROM DUAL UNION ALL
SELECT 'AAA','A2',233 FROM DUAL UNION ALL
SELECT 'AAA','A3',332 FROM DUAL UNION ALL
SELECT 'BBB','B1',288 FROM DUAL UNION ALL
SELECT 'BBB','B2',488 FROM DUAL UNION ALL
SELECT 'BBB','B3',588 FROM DUAL UNION ALL
SELECT 'BBB','B4',688 FROM DUAL UNION ALL
SELECT 'CCC','C1',555 FROM DUAL UNION ALL
SELECT 'CCC','C2',666 FROM DUAL
)
SELECT A,B,C FROM T1
MODEL
PARTITION BY (A)
DIMENSION BY (B,TO_CHAR(C) C)
MEASURES(1 N)
RULES(N[' ',' ']=0)
ORDER BY 1,N,2,3
;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/13696961/viewspace-2073849/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/13696961/viewspace-2073849/