大家好!有个问题请教大家。利用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
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
解决方案 »
- sqlplus 连不到数据库
- 关于ORACLE和MS SQL Server数据交换的问题
- 求教一个更新的问题!(杨哥,快来啊)
- 层次查询SQL语句
- oracle9i ora-12541:TNS:no listener ?
- 请问SQL *FORMS 和 SQL *REPORT这两个工具如何启动啊?初学者提问,有劳各位前辈!
- IBM P610 AIX5:0509-124 The program is a discontinued 64-bit object file问题请教,在线等待,急急急
- 求在Redhat9.0下安装ORACLE8.1.7时ORACLE的配置文件?
- 请问如何调用一个自定函数?谢谢!
- oracle突然查询很慢
- 请教xmltype查询计算的问题
- ora-00204错误,求高手解决??
你可以看看加上well_id = 'kGoWjgknhb'这个条件过,过滤掉哪些数据,尤其是涉及到空的
感谢关注:
1、将所有空的记录删除后,还存在此问题。
2、正在测试 一个well_id、inj_date分区内,completion_number存在重复 的问题。