c**t
发帖数: 2744 | 1 目的：create a view
数据库: oracle
输入: tbl
DT Code Type Value
d1 RM1 A ###
d1 RM2 A ###
d1 RM3 ###
d2 RM1 B ###
d2 RM2 B ###
d2 RM3 B ###
输出:
DT Code TYPE Value
d1 RA1 A ###
d1 RA2 A ###
d1 RA3 A ###
d1 RB1 B 0
d1 RB2 B 0
d1 RB3 B 0
d2 RA1 A 0
d2 RA2 A 0
d2 RA3 | B*****g
发帖数: 34098 | 2 看到包子，先站个坑，这个解法挺烂。
CREATE OR REPLACE FORCE VIEW beijing_test_view
(DT, CODE, TYPES, VALUE) AS
WITH TEMP_A AS
(SELECT DISTINCT DT FROM beijing_test),
TEMP_B AS
(SELECT 'RA1' CODE, 'A' TYPES FROM DUAL
UNION ALL
SELECT 'RA2' CODE, 'A' TYPES FROM DUAL
UNION ALL
SELECT 'RA3' CODE, 'A' TYPES FROM DUAL
UNION ALL
SELECT 'RB1' CODE, 'B' TYPES FROM DUAL
UNION ALL
SELECT 'RB2' CODE, 'B' TYPES FROM DUAL
UNION ALL
SELECT 'RB3' CODE, 'B' TYPES FROM DUAL),
TEMP_C AS
(SELECT * FROM TEMP_A, TEMP_B)
SELECT b.dt, b
【在 c**t 的大作中提到】
: 目的：create a view
: 数据库: oracle
: 输入: tbl
: DT Code Type Value
: d1 RM1 A ###
: d1 RM2 A ###
: d1 RM3 ###
: d2 RM1 B ###
: d2 RM2 B ###
: d2 RM3 B ###
| c**t
发帖数: 2744 | 3 too much union.. my solution:
CREATE OR REPLACE FORCE VIEW cogt_view
(DT, CODE, TYPES, VALUE) AS
WITH TMP AS
(SELECT DT, CODE, MAX(TYPE) over (PARTITION BY DT) as tmpType,
SUM( DECODE( TYPE, '', 0, 1) ) over (PARTITION BY DT) as tmpCnt
FROM TBL)
select DT, REPLACE(CODE, 'M', tmpType) as CODE, tmpType, VALUE from TMP
UNION ALL
select DT, REPLACE(CODE, 'M', decode(tmpType, 'A', 'B', 'A')) as CODE,
decode(tmpType, 'A', 'B', 'A') as TYPE, 0 from TMP
【在 B*****g 的大作中提到】
: 看到包子，先站个坑，这个解法挺烂。
: CREATE OR REPLACE FORCE VIEW beijing_test_view
: (DT, CODE, TYPES, VALUE) AS
: WITH TEMP_A AS
: (SELECT DISTINCT DT FROM beijing_test),
: TEMP_B AS
: (SELECT 'RA1' CODE, 'A' TYPES FROM DUAL
: UNION ALL
: SELECT 'RA2' CODE, 'A' TYPES FROM DUAL
: UNION ALL
|
|
