Oracle使用MAX(decode)存在BUG?
大家好!有个问题请教大家。利用max(decode)处理行转列
建表语句如下:
create table XX_PC_INJ_COMPLETION_DAILY_T
(
WELL_ID VARCHAR2(10) not null,
INJ_DATE DATE not null,
COMPLETION_ID VARCHAR2(10) not null,
PROPERTY_ID VARCHAR2(5),
CHOKE_SIZE NVARCHAR2(20),
INTERVAL_ALLOC_INJ_WATER_DAILY NUMBER,
INJ_VOL_DAILY NUMBER,
COMPLETION_NUMBER NUMBER
)
max(decode) SQL 如下:
select
well_id,
inj_date,
max(decode(completion_number,1,inj_vol_daily )) inj_vol_daily1,
max(decode(completion_number,1,interval_alloc_inj_water_daily )) alloc_inj_vol_daily1,
max(decode(completion_number,2,inj_vol_daily )) inj_vol_daily2,
max(decode(completion_number,2,interval_alloc_inj_water_daily )) alloc_inj_vol_daily2
...
from
xx_pc_inj_completion_daily_t
group by well_id,inj_date
问题如下:
个别记录行转列失效.
举例源表select .. from xx_pc_inj_completion_daily_t
where well_id = 'kGoWjgknhb' and completion_number=1 存在记录.
但max(decode)的结果不存在completion_number等于1的列.
源表记录:
[code=SQL]
WELL_ID INJ_DATE INTERVAL_ALLOC_INJ_WATER_DAILY INJ_VOL_DAILY COMPLETION_NUMBER
kGoWjgknhb 2011-1-1 20 18 4
kGoWjgknhb 2011-1-1 60 18 1
kGoWjgknhb 2011-1-1 50 9 3
kGoWjgknhb 2011-1-1 35 17 2
/code]
Max(decode)结果:
[code=SQL]
WELL_ID INJ_DATE INTERVAL_ALLOC_INJ_WATER_DAILY1 INJ_VOL_DAILY1 INTERVAL_ALLOC_INJ_WATER_DAILY2 INJ_VOL_DAILY2
kGoWjgknhb 2011-1-1 35 17
/code]
如果对源表增加过滤条件,max(decode)正常。
select
well_id,
inj_date,
max(decode(completion_number,1,inj_vol_daily )) inj_vol_daily1,
max(decode(completion_number,1,interval_alloc_inj_water_daily )) alloc_inj_vol_daily1,
max(decode(completion_number,2,inj_vol_daily )) inj_vol_daily2,
max(decode(completion_number,2,interval_alloc_inj_water_daily )) alloc_inj_vol_daily2
...
from
xx_pc_inj_completion_daily_t
where well_id = 'kGoWjgknhb'
group by well_id,inj_date
查询出的结果completion_number等于1的列存在。
[code=SQL]
WELL_ID INJ_DATE INTERVAL_ALLOC_INJ_WATER_DAILY1 INJ_VOL_DAILY1 INTERVAL_ALLOC_INJ_WATER_DAILY2 INJ_VOL_DAILY2
kGoWjgknhb 2011-1-1 60 18 35 17
/code]
我怀疑可能有以下原因导致,请大家帮助:
1、表数据量太多,导致MAX(DECODE)出问题。 该表有500W行记录
2、表的completion_number列存在空
3、一个well_id、inj_date分区内,completion_number存在重复
例如:
WELL_ID INJ_DATE completion_number
kGoWjgknhb 2011-5-1 ... 1
kGoWjgknhb 2011-5-1 ... 1