[求助]一个既简单又不简单的问题

Question

各位大牛帮帮忙呀。问题看似简单，但是有不简单的要求。问题描述如下：
表wp_001有三个字段(id, id1, cnt),表wp_002有一个字段(id1)。其中wp_001的id1值是wp_002的一个子集。
现在要求的是：得到wp_001表中id对应wp_002的每一个id1下的cnt,由于wp_001中的id1是wp_002的一个子集，对于wp_002中不在wp_001中出现的id1对应的cnt值，取wp_001中原有cnt的平均值。
各个表结构如下：SQL> select * from wp_001;        ID        ID1        CNT
---------- ---------- ----------
       111          1          5
       111          2          6
       111          3          7
       112          1          8
       
SQL> select * from wp_002;       ID1
----------
         1
         2
         3
         4最终要得到的结果如下：
        ID        ID1        CNT
---------- ---------- ----------
       111          1          5
       111          2          6
       111          3          7
       111          4          6
       112          1          8
       112          2          8
       112          3          8
       112          4          8
       
注：不简单的地方是只能利用wp_001和wp_002，不能建立其他任何临时表

Answer 1

select a.id,a.id1,
  case when b.cnt is not null then b.cnt
  else avg(b.cnt)over(partition by a.id) end cnt
from (select * from (select distinct id from wp_001),wp_002)a,
wp_001 b
where a.id1=b.id1(+) and a.id=b.id(+)
order by id,id1

Answer 2

参考sql:
SELECT ws.id,ws.id1,nvl(w1.cnt,(select  avg(cnt) from wp_001 wp1 where wp1.id=ws.id group by wp1.id)) cnt FROM (SELECT w2.id1,ws.id FROM wp_002 w2 CROSS JOIN (SELECT wp.id FROM wp_001 wp group by wp.id) ws ) ws
left join wp_001 w1
on ws.id=w1.id
and ws.id1=w1.id1

Answer 3

with temp as (
select 111 id1, 1 id, 5 CNT from dual
union all
select 111 id1, 2 id, 6 CNT from dual
union all
select 111 id1, 4 id, 7 CNT from dual
union all
select 112 id1, 1 id, 3 CNT from dual
), temp1 as (
select 1 id from dual
union all
select 2 id from dual
union all
select 3 id from dual
union all
select 4 id from dual
)
select b.id1, b.id, nvl(a.cnt, avg(a.cnt) over(partition by b.id1)) from temp a right join (select c.id1, temp1.id from (select distinct id1 from temp) c, temp1) b on a.id=b.id and a.id1=b.id1

Answer 4

首先感谢你的回复。
但是你的代码中的a表其实也是占用临时空间的。如果select distinct idfrom wp_001的结果是千万级的数据量，那么这个a表就会非常大。运行的效率会很慢，考虑这种情况，所以我想的是不占用临时空间。难就难在这个不占用临时空间。

Answer 5

因为wp_001中的id1不完全，需要把id1补全，最后得到id在全部id1下的cnt。

Answer 6

这个补全是要insert到原表中吗
只是查询的话，结果集这么大就没有意义了吧