包子题 (1st answer 100新币) - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>Database - 数据库

包子题 (1st answer 100新币)

包子题 (1st answer 100新币)# Database - 数据库

c*t2009-03-12 07:03

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*g2009-03-12 07:03

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*t2009-03-12 07:03

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