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 dualunion allselect
'select z01_111,'
from dual union all select distinct
'c'
||
z01_08
||
decode
(
z01_08
,(
select ma from mst
),
''
,
','
)
from lstunion all select
'from (select distinct z01_111 from z112 order by 1) a left join '
from dualunion 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
,
c130from
(
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 dualunion allselect
'select z01_08,'
from dual union all select distinct
'c'
||
z01_111
||
decode
(
z01_111
,(
select ma from mst
),
''
,
','
)
from lstunion all select
'from (select distinct z01_08 from z112 order by 1) a left join '
from dualunion 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
,
c2002from
(
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 1130 1 1110 1122 1SQL
>
