SQL
>
select
*
from j1
;
Z01_111 Z01
---------- ---
1990 110
1991 120
1989 130
2001 130
2002 120
1998 122
已选择6行。
利用oracle的with语句和动态sql
执行下面的
with lst as ( select distinct z01_08 from j1 order by z01_08 ),
mst as ( select max ( z01_08 ) ma from j1 )
select 'with z112 as (select z01_111,z01_08, count(z01_111) s from j1 group by z01_08,z01_111) ' from dual
union all
select 'select z01_111,' from dual
union all
select distinct 'c' || z01_08 || decode ( z01_08 ,( select ma from mst ), '' , ',' ) from lst
union all
select 'from (select distinct z01_111 from z112 order by 1) a left join ' from dual
union all
select '(select z01_111,s c' || z01_08 || ' from z112 where z01_08=''' || z01_08 || ''') '
|| 'a' || z01_08 || ' using (z01_111) ' || decode ( z01_08 ,( select ma from mst ), ';' , 'left join ' ) from lst ;
产生
'WITHZ112AS(SELECTZ01_111,Z01_08,COUNT(Z01_111)SFROMJ1GROUPBYZ01_08,Z01_111)'
---------------------------------------------------------------------------------------
with z112 as ( select z01_111 , z01_08 , count ( z01_111 ) s from j1 group by z01_08 , z01_111 )
select z01_111 ,
c110 ,
c120 ,
c122 ,
c130
from ( select distinct z01_111 from z112 order by 1 ) a left join
( select z01_111 , s c110 from z112 where z01_08 = '110' ) a110 using ( z01_111 ) left join
( select z01_111 , s c120 from z112 where z01_08 = '120' ) a120 using ( z01_111 ) left join
( select z01_111 , s c122 from z112 where z01_08 = '122' ) a122 using ( z01_111 ) left join
( select z01_111 , s c130 from z112 where z01_08 = '130' ) a130 using ( z01_111 ) ;
已选择11行。
再执行产生的语句
SQL > with z112 as ( select z01_111 , z01_08 , count ( z01_111 ) s from j1 group by z01_08 , z01_111 )
2 select z01_111 ,
3 c110 ,
4 c120 ,
5 c122 ,
6 c130
7 from ( select distinct z01_111 from z112 order by 1 ) a left join
8 ( select z01_111 , s c110 from z112 where z01_08 = '110' ) a110 using ( z01_111 ) left join
9 ( select z01_111 , s c120 from z112 where z01_08 = '120' ) a120 using ( z01_111 ) left join
10 ( select z01_111 , s c122 from z112 where z01_08 = '122' ) a122 using ( z01_111 ) left join
11 ( select z01_111 , s c130 from z112 where z01_08 = '130' ) a130 using ( z01_111 ) ;
Z01_111 C110 C120 C122 C130
---------- ---------- ---------- ---------- ----------
1989 1
2001 1
1991 1
1990 1
1998 1
2002 1
已选择6行。
SQL > create view tabu as with z112 as ( select z01_111 , z01_08 , count ( z01_111 ) s from j1 group by z01_08 , z01_111 )
2 select z01_111 ,
3 c110 ,
4 c120 ,
5 c122 ,
6 c130
7 from ( select distinct z01_111 from z112 order by 1 ) a left join
8 ( select z01_111 , s c110 from z112 where z01_08 = '110' ) a110 using ( z01_111 ) left join
9 ( select z01_111 , s c120 from z112 where z01_08 = '120' ) a120 using ( z01_111 ) left join
10 ( select z01_111 , s c122 from z112 where z01_08 = '122' ) a122 using ( z01_111 ) left join
11 ( select z01_111 , s c130 from z112 where z01_08 = '130' ) a130 using ( z01_111 ) ;
视图已建立。
SQL > select * from tabu ;
Z01_111 C110 C120 C122 C130
---------- ---------- ---------- ---------- ----------
1989 1
2001 1
1991 1
1990 1
1998 1
2002 1
已选择6行。
如果要把交叉表的行列互换,只要交换语句中的z01_08和z01_111
with lst as ( select distinct z01_111 from j1 order by z01_111 ),
mst as ( select max ( z01_111 ) ma from j1 )
select 'with z112 as (select z01_08,z01_111, count(z01_08) s from j1 group by z01_111,z01_08) ' from dual
union all
select 'select z01_08,' from dual
union all
select distinct 'c' || z01_111 || decode ( z01_111 ,( select ma from mst ), '' , ',' ) from lst
union all
select 'from (select distinct z01_08 from z112 order by 1) a left join ' from dual
union all
select '(select z01_08,s c' || z01_111 || ' from z112 where z01_111=''' || z01_111 || ''') '
|| 'a' || z01_111 || ' using (z01_08) ' || decode ( z01_111 ,( select ma from mst ), ';' , 'left join ' ) from lst ;
执行后产生
'WITHZ112AS(SELECTZ01_08,Z01_111,COUNT(Z01_08)SFROMJ1GROUPBYZ01_111,Z01_08)'
-------------------------------------------------------------------------------------------------------
with z112 as ( select z01_08 , z01_111 , count ( z01_08 ) s from j1 group by z01_111 , z01_08 )
select z01_08 ,
c1989 ,
c1990 ,
c1991 ,
c1998 ,
c2001 ,
c2002
from ( select distinct z01_08 from z112 order by 1 ) a left join
( select z01_08 , s c1989 from z112 where z01_111 = '1989' ) a1989 using ( z01_08 ) left join
( select z01_08 , s c1990 from z112 where z01_111 = '1990' ) a1990 using ( z01_08 ) left join
( select z01_08 , s c1991 from z112 where z01_111 = '1991' ) a1991 using ( z01_08 ) left join
( select z01_08 , s c1998 from z112 where z01_111 = '1998' ) a1998 using ( z01_08 ) left join
( select z01_08 , s c2001 from z112 where z01_111 = '2001' ) a2001 using ( z01_08 ) left join
( select z01_08 , s c2002 from z112 where z01_111 = '2002' ) a2002 using ( z01_08 ) ;
已选择15行。
SQL > with z112 as ( select z01_08 , z01_111 , count ( z01_08 ) s from j1 group by z01_111 , z01_08 )
2 select z01_08 ,
3 c1989 ,
4 c1990 ,
5 c1991 ,
6 c1998 ,
7 c2001 ,
8 c2002
9 from ( select distinct z01_08 from z112 order by 1 ) a left join
10 ( select z01_08 , s c1989 from z112 where z01_111 = '1989' ) a1989 using ( z01_08 ) left join
11 ( select z01_08 , s c1990 from z112 where z01_111 = '1990' ) a1990 using ( z01_08 ) left join
12 ( select z01_08 , s c1991 from z112 where z01_111 = '1991' ) a1991 using ( z01_08 ) left join
13 ( select z01_08 , s c1998 from z112 where z01_111 = '1998' ) a1998 using ( z01_08 ) left join
14 ( select z01_08 , s c2001 from z112 where z01_111 = '2001' ) a2001 using ( z01_08 ) left join
15 ( select z01_08 , s c2002 from z112 where z01_111 = '2002' ) a2002 using ( z01_08 ) ;
Z01 C1989 C1990 C1991 C1998 C2001 C2002
--- ---------- ---------- ---------- ---------- ---------- ----------
120 1 1
130 1 1
110 1
122 1
SQL >
Z01_111 Z01
---------- ---
1990 110
1991 120
1989 130
2001 130
2002 120
1998 122
已选择6行。
利用oracle的with语句和动态sql
执行下面的
with lst as ( select distinct z01_08 from j1 order by z01_08 ),
mst as ( select max ( z01_08 ) ma from j1 )
select 'with z112 as (select z01_111,z01_08, count(z01_111) s from j1 group by z01_08,z01_111) ' from dual
union all
select 'select z01_111,' from dual
union all
select distinct 'c' || z01_08 || decode ( z01_08 ,( select ma from mst ), '' , ',' ) from lst
union all
select 'from (select distinct z01_111 from z112 order by 1) a left join ' from dual
union all
select '(select z01_111,s c' || z01_08 || ' from z112 where z01_08=''' || z01_08 || ''') '
|| 'a' || z01_08 || ' using (z01_111) ' || decode ( z01_08 ,( select ma from mst ), ';' , 'left join ' ) from lst ;
产生
'WITHZ112AS(SELECTZ01_111,Z01_08,COUNT(Z01_111)SFROMJ1GROUPBYZ01_08,Z01_111)'
---------------------------------------------------------------------------------------
with z112 as ( select z01_111 , z01_08 , count ( z01_111 ) s from j1 group by z01_08 , z01_111 )
select z01_111 ,
c110 ,
c120 ,
c122 ,
c130
from ( select distinct z01_111 from z112 order by 1 ) a left join
( select z01_111 , s c110 from z112 where z01_08 = '110' ) a110 using ( z01_111 ) left join
( select z01_111 , s c120 from z112 where z01_08 = '120' ) a120 using ( z01_111 ) left join
( select z01_111 , s c122 from z112 where z01_08 = '122' ) a122 using ( z01_111 ) left join
( select z01_111 , s c130 from z112 where z01_08 = '130' ) a130 using ( z01_111 ) ;
已选择11行。
再执行产生的语句
SQL > with z112 as ( select z01_111 , z01_08 , count ( z01_111 ) s from j1 group by z01_08 , z01_111 )
2 select z01_111 ,
3 c110 ,
4 c120 ,
5 c122 ,
6 c130
7 from ( select distinct z01_111 from z112 order by 1 ) a left join
8 ( select z01_111 , s c110 from z112 where z01_08 = '110' ) a110 using ( z01_111 ) left join
9 ( select z01_111 , s c120 from z112 where z01_08 = '120' ) a120 using ( z01_111 ) left join
10 ( select z01_111 , s c122 from z112 where z01_08 = '122' ) a122 using ( z01_111 ) left join
11 ( select z01_111 , s c130 from z112 where z01_08 = '130' ) a130 using ( z01_111 ) ;
Z01_111 C110 C120 C122 C130
---------- ---------- ---------- ---------- ----------
1989 1
2001 1
1991 1
1990 1
1998 1
2002 1
已选择6行。
SQL > create view tabu as with z112 as ( select z01_111 , z01_08 , count ( z01_111 ) s from j1 group by z01_08 , z01_111 )
2 select z01_111 ,
3 c110 ,
4 c120 ,
5 c122 ,
6 c130
7 from ( select distinct z01_111 from z112 order by 1 ) a left join
8 ( select z01_111 , s c110 from z112 where z01_08 = '110' ) a110 using ( z01_111 ) left join
9 ( select z01_111 , s c120 from z112 where z01_08 = '120' ) a120 using ( z01_111 ) left join
10 ( select z01_111 , s c122 from z112 where z01_08 = '122' ) a122 using ( z01_111 ) left join
11 ( select z01_111 , s c130 from z112 where z01_08 = '130' ) a130 using ( z01_111 ) ;
视图已建立。
SQL > select * from tabu ;
Z01_111 C110 C120 C122 C130
---------- ---------- ---------- ---------- ----------
1989 1
2001 1
1991 1
1990 1
1998 1
2002 1
已选择6行。
如果要把交叉表的行列互换,只要交换语句中的z01_08和z01_111
with lst as ( select distinct z01_111 from j1 order by z01_111 ),
mst as ( select max ( z01_111 ) ma from j1 )
select 'with z112 as (select z01_08,z01_111, count(z01_08) s from j1 group by z01_111,z01_08) ' from dual
union all
select 'select z01_08,' from dual
union all
select distinct 'c' || z01_111 || decode ( z01_111 ,( select ma from mst ), '' , ',' ) from lst
union all
select 'from (select distinct z01_08 from z112 order by 1) a left join ' from dual
union all
select '(select z01_08,s c' || z01_111 || ' from z112 where z01_111=''' || z01_111 || ''') '
|| 'a' || z01_111 || ' using (z01_08) ' || decode ( z01_111 ,( select ma from mst ), ';' , 'left join ' ) from lst ;
执行后产生
'WITHZ112AS(SELECTZ01_08,Z01_111,COUNT(Z01_08)SFROMJ1GROUPBYZ01_111,Z01_08)'
-------------------------------------------------------------------------------------------------------
with z112 as ( select z01_08 , z01_111 , count ( z01_08 ) s from j1 group by z01_111 , z01_08 )
select z01_08 ,
c1989 ,
c1990 ,
c1991 ,
c1998 ,
c2001 ,
c2002
from ( select distinct z01_08 from z112 order by 1 ) a left join
( select z01_08 , s c1989 from z112 where z01_111 = '1989' ) a1989 using ( z01_08 ) left join
( select z01_08 , s c1990 from z112 where z01_111 = '1990' ) a1990 using ( z01_08 ) left join
( select z01_08 , s c1991 from z112 where z01_111 = '1991' ) a1991 using ( z01_08 ) left join
( select z01_08 , s c1998 from z112 where z01_111 = '1998' ) a1998 using ( z01_08 ) left join
( select z01_08 , s c2001 from z112 where z01_111 = '2001' ) a2001 using ( z01_08 ) left join
( select z01_08 , s c2002 from z112 where z01_111 = '2002' ) a2002 using ( z01_08 ) ;
已选择15行。
SQL > with z112 as ( select z01_08 , z01_111 , count ( z01_08 ) s from j1 group by z01_111 , z01_08 )
2 select z01_08 ,
3 c1989 ,
4 c1990 ,
5 c1991 ,
6 c1998 ,
7 c2001 ,
8 c2002
9 from ( select distinct z01_08 from z112 order by 1 ) a left join
10 ( select z01_08 , s c1989 from z112 where z01_111 = '1989' ) a1989 using ( z01_08 ) left join
11 ( select z01_08 , s c1990 from z112 where z01_111 = '1990' ) a1990 using ( z01_08 ) left join
12 ( select z01_08 , s c1991 from z112 where z01_111 = '1991' ) a1991 using ( z01_08 ) left join
13 ( select z01_08 , s c1998 from z112 where z01_111 = '1998' ) a1998 using ( z01_08 ) left join
14 ( select z01_08 , s c2001 from z112 where z01_111 = '2001' ) a2001 using ( z01_08 ) left join
15 ( select z01_08 , s c2002 from z112 where z01_111 = '2002' ) a2002 using ( z01_08 ) ;
Z01 C1989 C1990 C1991 C1998 C2001 C2002
--- ---------- ---------- ---------- ---------- ---------- ----------
120 1 1
130 1 1
110 1
122 1
SQL >