我使用的是Oracle 9i SQL数据库。我想写这样的查询
select
id_process_inst,<br>
max(case when id_data=34756 then value_text end),<br>
max(case when id_data=34760 then value_text end),<br>
max(case when id_data=34793 then value_text end),<br>
max(case when id_data=34792 then value_text end),<br>
max(case when id_data=34790 then value_text end),<br>
max(case when id_data=34761 then value_text end),<br>
max(case when id_data=34791 then value_text end),<br>
max(case when id_data=34766 then value_text end),<br>
max(case when id_data=34778 then value_text end),<br>
max(case when id_data=34767 then value_text end),<br>
m....<br>
from<br>
(<br>
select <br>
procreldata.id_inst,<br>
dataset.value_text,<br>
procreldata.id_data<br>
from wfdata procreldata<br>
inner join wfvalue dataset<br>
on dataset.id_data= procreldata.id_data<br>
where procreldata.id_inst=177262<br>
)<br>
group by id_inst;<br>
每当我尝试执行此查询时，都会出现"排序键过长"错误。我想这是因为我在这个查询中使用了大约32个最大函数
请帮我解决这个问题
提前谢谢。
我链接到的Ask Tom文章表明，这是数据值和列数的问题，这里显示的例子似乎确实是这样；但在这种情况下，它显然只是列的数量。
这是一个真正的破解，但似乎有效。这里t42有效地表示了实际表之间的联接结果。如果我创建了那个伪表，并添加了一些数据最少的行：
create table t42 (inst_id number, id_data number, value_text varchar2(4000));
insert into t42 values (1, 1, 'w');
insert into t42 values (1, 2, 'x');
insert into t42 values (1, 3, 'y');
insert into t42 values (1, 4, 'z');
然后这仍然失败：
select max(case when id_data = 1 then value_text end),
max(case when id_data = 2 then value_text end),
max(case when id_data = 3 then value_text end),
max(case when id_data = 4 then value_text end),
max(case when id_data = 5 then value_text end),
max(case when id_data = 6 then value_text end),
max(case when id_data = 7 then value_text end),
max(case when id_data = 8 then value_text end),
max(case when id_data = 9 then value_text end),
max(case when id_data = 10 then value_text end),
max(case when id_data = 11 then value_text end),
max(case when id_data = 12 then value_text end),
max(case when id_data = 13 then value_text end),
max(case when id_data = 14 then value_text end),
max(case when id_data = 15 then value_text end),
max(case when id_data = 16 then value_text end)
from t42
group by inst_id;
from t42
*
ERROR at line 17:
ORA-01467: sort key too long
我只需要16个聚合表达式，我想这仍然与我的块大小有关，在我仅有的9i数据库中，块大小为2k。如果你需要32列才能得到错误，那么我猜你的块大小是4k。除非你能提高它，否则不确定这真的很重要。
经过一点实验，这是我发现的第一个可行的替代方案：
with t as (
select inst_id, id_data,
case when id_data = 1 then value_text end as x1,
case when id_data = 2 then value_text end as x2,
case when id_data = 3 then value_text end as x3,
case when id_data = 4 then value_text end as x4,
case when id_data = 5 then value_text end as x5,
case when id_data = 6 then value_text end as x6,
case when id_data = 7 then value_text end as x7,
case when id_data = 8 then value_text end as x8,
case when id_data = 9 then value_text end as x9,
case when id_data = 10 then value_text end as x10,
case when id_data = 11 then value_text end as x11,
case when id_data = 12 then value_text end as x12,
case when id_data = 13 then value_text end as x13,
case when id_data = 14 then value_text end as x14,
case when id_data = 15 then value_text end as x15,
case when id_data = 16 then value_text end as x16
from t42
)
select (select max(x1) from t t2 where t2.inst_id = t.inst_id),
(select max(x2) from t t2 where t2.inst_id = t.inst_id),
(select max(x3) from t t2 where t2.inst_id = t.inst_id),
(select max(x4) from t t2 where t2.inst_id = t.inst_id),
(select max(x5) from t t2 where t2.inst_id = t.inst_id),
(select max(x6) from t t2 where t2.inst_id = t.inst_id),
(select max(x7) from t t2 where t2.inst_id = t.inst_id),
(select max(x8) from t t2 where t2.inst_id = t.inst_id),
(select max(x9) from t t2 where t2.inst_id = t.inst_id),
(select max(x10) from t t2 where t2.inst_id = t.inst_id),
(select max(x11) from t t2 where t2.inst_id = t.inst_id),
(select max(x12) from t t2 where t2.inst_id = t.inst_id),
(select max(x13) from t t2 where t2.inst_id = t.inst_id),
(select max(x14) from t t2 where t2.inst_id = t.inst_id),
(select max(x15) from t t2 where t2.inst_id = t.inst_id),
(select max(x16) from t t2 where t2.inst_id = t.inst_id)
from t
group by inst_id;
(SEL (SEL (SEL (SEL (SEL (SEL (SEL (SEL (SEL (SEL (SEL (SEL (SEL (SEL (SEL (SEL
---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
w x y z
所有的自联接都很痛苦，但至少它们在主查询的结果集中，所以你仍然只访问一次真正的表——其余的只需要足够的内存来管理所有返回的数据。
由于现在在主查询中没有聚合，只有在子查询中，所以使用distinct而不是group by可能更清楚，尽管效果是一样的：
...
select distinct inst_id,
(select max(x1) from t t2 where t2.inst_id = t.inst_id),
...
(select max(x16) from t t2 where t2.inst_id = t.inst_id)
from t;
这只是一个示范。在我使用t42作为假数据的占位符的地方，将原始查询放回，并根据需要添加任意数量的聚合；因此在with子句中：
with t as (
select inst_id, id_data,
case when id_data = 34756 then value_text end as x1,
...
case when id_data = 34999 then value_text end as x32
from (
select procreldata.id_inst,
dataset.value_text,
procreldata.id_data
from wfdata procreldata
inner join wfvalue dataset
on dataset.id_data= procreldata.id_data
where procreldata.id_inst=177262
)
)
select distinct inst_id,
(select max(x1) from t t2 where t2.inst_id = t.inst_id),
...
(select max(x32) from t t2 where t2.inst_id = t.inst_id)
from t;
当然，我并不是说它很漂亮。。。