如何用sql中with语法来虚拟产生表

with --with用来虚拟了两个表
PLAYERS AS (SELECT 'P'||ROWNUM USERNAME FROM ALL_OBJECTS WHERE ROWNUM<=8)
,WEEKS AS (SELECT ROWNUM WEEK FROM ALL_OBJECTS WHERE ROWNUM<=7)
select week,
max(decode(rn,1,username,null)) u1,
max(decode(rn,2,username,null)) u2,
max(decode(rn,3,username,null)) u3,
max(decode(rn,4,username,null)) u4,
max(decode(rn,5,username,null)) u5,
max(decode(rn,6,username,null)) u6,
max(decode(rn,7,username,null)) u7,
max(decode(rn,8,username,null)) u8/*,
username,week,rn*/
from (select username,week,row_number()over (partition by week order by rnd)rn
from (select username,week,dbms_random.random rnd from players,weeks))
group by week

转载于:https://www.cnblogs.com/ebs-blog/archive/2007/10/22/2167356.html