原来的表数据如下:
TNAME CID CNAME TYPE PK UK GK CHK_NAME CHK_VAL NULLABLE
-------------------------------------------------------------------------------------------------------------------------------------
EMP 1 EMPNO NUMBER EMP_PK
EMP 1 EMPNO NUMBER EMPNO_UK
EMP 1 EMPNO NUMBER CHK11 "EMPNO"<>'1'
EMP 1 EMPNO NUMBER not null
EMP 2 ENAME NUMBER ENAME_SAL
EMP 2 ENAME NUMBER not null
EMP 2 ENAME NUMBER CHK22 ename<>'ABC'
EMP 2 ENAME NUMBER CHK33 ename<>'CLITON'
EMP 2 ENAME NUMBER ENAME_UK
EMP 2 ENAME NUMBER ENAME_JOB请问:如何才能变成下面的形式,即在col_name的PK、FK、UK等最大列数的情况下,对其它字段的值进行压缩。
TNAME CID CNAME TYPE PK UK GK CHK_NAME CHK_VAL NULLABLE
-------------------------------------------------------------------------------------------------------------------------------------------
EMP 1 EMPNO NUMBER 1 EMP_PK EMPNO_UK CHK11 "EMPNO"<>'1' not null
EMP 2 ENAME NUMBER 1 ENAME_SAL CHK22 ename<>'CLITON' not null
2 ENAME_UK ENAME_JOB CHK33 ename<>'ABC'
偶想了好几天,但还是没有解决掉,请大侠指点,小菜十分感谢!附表结构及SQL语句
----------------------------------------------------------------------------
create table T_STRUCT
(
TNAME VARCHAR2(50),
CID NUMBER,
CNAME VARCHAR2(50),
TYPE VARCHAR2(50),
PK VARCHAR2(50),
UK varchar2(50),
GK VARCHAR2(50),
CHK_NAME VARCHAR2(50),
CHK_VAL VARCHAR2(500),
NULLABLE VARCHAR2(10)
);insert into t_struct values('EMP',1,'EMPNO','NUMBER', 'EMP_PK',null,null,null,null,null);
insert into t_struct values('EMP',1,'EMPNO','NUMBER', null,'EMPNO_UK',null,null,null,null);
insert into t_struct values('EMP',1,'EMPNO','NUMBER', null,null,null,'CHK11','"EMPNO" IS NOT NULL',null);
insert into t_struct values('EMP',1,'EMPNO','NUMBER', null,null,null,null,null,'not null');
insert into t_struct values('EMP',2,'ENAME','NUMBER',null,'ENAME_SAL',null,null,null,null);
insert into t_struct values('EMP',2,'ENAME','NUMBER',null,null,null,null,null,'not null');
insert into t_struct values('EMP',2,'ENAME','NUMBER',null,null,null,'CHK_ENAME2','ename<>''ABC''',null);
insert into t_struct values('EMP',2,'ENAME','NUMBER',null,null,null,'CHK_ENAME1','ename<>''CLITON''',null);
insert into t_struct values('EMP',2,'ENAME','NUMBER',null,'ENAME_UK',null,null,null,null);
insert into t_struct values('EMP',2,'ENAME','NUMBER',null,null,'IDX_ENAME_JOB',null,null,null);commit;
TNAME CID CNAME TYPE PK UK GK CHK_NAME CHK_VAL NULLABLE
-------------------------------------------------------------------------------------------------------------------------------------
EMP 1 EMPNO NUMBER EMP_PK
EMP 1 EMPNO NUMBER EMPNO_UK
EMP 1 EMPNO NUMBER CHK11 "EMPNO"<>'1'
EMP 1 EMPNO NUMBER not null
EMP 2 ENAME NUMBER ENAME_SAL
EMP 2 ENAME NUMBER not null
EMP 2 ENAME NUMBER CHK22 ename<>'ABC'
EMP 2 ENAME NUMBER CHK33 ename<>'CLITON'
EMP 2 ENAME NUMBER ENAME_UK
EMP 2 ENAME NUMBER ENAME_JOB请问:如何才能变成下面的形式,即在col_name的PK、FK、UK等最大列数的情况下,对其它字段的值进行压缩。
TNAME CID CNAME TYPE PK UK GK CHK_NAME CHK_VAL NULLABLE
-------------------------------------------------------------------------------------------------------------------------------------------
EMP 1 EMPNO NUMBER 1 EMP_PK EMPNO_UK CHK11 "EMPNO"<>'1' not null
EMP 2 ENAME NUMBER 1 ENAME_SAL CHK22 ename<>'CLITON' not null
2 ENAME_UK ENAME_JOB CHK33 ename<>'ABC'
偶想了好几天,但还是没有解决掉,请大侠指点,小菜十分感谢!附表结构及SQL语句
----------------------------------------------------------------------------
create table T_STRUCT
(
TNAME VARCHAR2(50),
CID NUMBER,
CNAME VARCHAR2(50),
TYPE VARCHAR2(50),
PK VARCHAR2(50),
UK varchar2(50),
GK VARCHAR2(50),
CHK_NAME VARCHAR2(50),
CHK_VAL VARCHAR2(500),
NULLABLE VARCHAR2(10)
);insert into t_struct values('EMP',1,'EMPNO','NUMBER', 'EMP_PK',null,null,null,null,null);
insert into t_struct values('EMP',1,'EMPNO','NUMBER', null,'EMPNO_UK',null,null,null,null);
insert into t_struct values('EMP',1,'EMPNO','NUMBER', null,null,null,'CHK11','"EMPNO" IS NOT NULL',null);
insert into t_struct values('EMP',1,'EMPNO','NUMBER', null,null,null,null,null,'not null');
insert into t_struct values('EMP',2,'ENAME','NUMBER',null,'ENAME_SAL',null,null,null,null);
insert into t_struct values('EMP',2,'ENAME','NUMBER',null,null,null,null,null,'not null');
insert into t_struct values('EMP',2,'ENAME','NUMBER',null,null,null,'CHK_ENAME2','ename<>''ABC''',null);
insert into t_struct values('EMP',2,'ENAME','NUMBER',null,null,null,'CHK_ENAME1','ename<>''CLITON''',null);
insert into t_struct values('EMP',2,'ENAME','NUMBER',null,'ENAME_UK',null,null,null,null);
insert into t_struct values('EMP',2,'ENAME','NUMBER',null,null,'IDX_ENAME_JOB',null,null,null);commit;
select a.TNAME,
a.CID ,
a.CNAME ,
a.TYPE,
a.PK,
b.UK,
c.GK,
d.CHK_NAME,
e.CHK_VAL,
f.NULLABLE from
(
select TNAME, CID , CNAME ,TYPE,PK
from table a
a. PK is not null
group by TNAME, CID , CNAME ,TYPE,PK
) a,(
select TNAME, CID , CNAME ,TYPE,UK
from table a
a. uK is not null
group by TNAME, CID , CNAME ,TYPE,UK
) b,
(
select TNAME, CID , CNAME ,TYPE,GK
from table a
a. GK is not null
group by TNAME, CID , CNAME ,TYPE,GK
) c,
(
select TNAME, CID , CNAME ,TYPE,CHK_NAME
from table a
a. CHK_NAME is not null
group by TNAME, CID , CNAME ,TYPE,CHK_NAME
) d,
(
select TNAME, CID , CNAME ,TYPE,CHK_VAL
from table a
a. CHK_VAL is not null
group by TNAME, CID , CNAME ,TYPE,CHK_VAL
) e,
(
select TNAME, CID , CNAME ,TYPE,NULLABLE
from table a
a. NULLABLE is not null
group by TNAME, CID , CNAME ,TYPE,NULLABLE
) fwhere a.TNAME=b.TNAME
and b.TNAME=c.TNAME
and c.TNAME=d.TNAME
and d.TNAME=e.TNAME
and e.TNAME=f.TNAME
and a.CID =b.CID
and b.CID =c.CID
and c.CID =d.CID
and d.CID =e.CID
and e.CID =f.CID
and a.CNAME=b.CNAME
and b.CNAME=c.CNAME
and c.CNAME=d.CNAME
and d.CNAME=e.CNAME
and e.CNAME=f.CNAME
and a.TYPE=b.TYPE
and b.TYPE=c.TYPE
and c.TYPE=d.TYPE
and d.TYPE=e.TYPE
and e.TYPE=f.TYPE
g.CID ,
g.CNAME ,
g.TYPE,
a.PK,
b.UK,
c.GK,
d.CHK_NAME,
e.CHK_VAL,
f.NULLABLE from
(
select TNAME, CID , CNAME ,TYPE,PK
from table a
a. PK is not null
group by TNAME, CID , CNAME ,TYPE,PK
) a,(
select TNAME, CID , CNAME ,TYPE,UK
from table a
a. uK is not null
group by TNAME, CID , CNAME ,TYPE,UK
) b,
(
select TNAME, CID , CNAME ,TYPE,GK
from table a
a. GK is not null
group by TNAME, CID , CNAME ,TYPE,GK
) c,
(
select TNAME, CID , CNAME ,TYPE,CHK_NAME
from table a
a. CHK_NAME is not null
group by TNAME, CID , CNAME ,TYPE,CHK_NAME
) d,
(
select TNAME, CID , CNAME ,TYPE,CHK_VAL
from table a
a. CHK_VAL is not null
group by TNAME, CID , CNAME ,TYPE,CHK_VAL
) e,
(
select TNAME, CID , CNAME ,TYPE,NULLABLE
from table a
a. NULLABLE is not null
group by TNAME, CID , CNAME ,TYPE,NULLABLE
) f,select TNAME, CID , CNAME ,TYPE,NULLABLE
from table a
a. NULLABLE is not null
group by TNAME, CID , CNAME ,TYPE,NULLABLE
) g
where
g.TNAME=a.TNAME(+)
and g.TNAME=b.TNAME(+)
and g.TNAME=c.TNAME(+)
and g.TNAME=d.TNAME(+)
and g.TNAME=e.TNAME(+)
and g.TNAME=f.TNAME(+)
and g.CID =a.CID(+)
and g.CID =b.CID(+)
and g.CID =c.CID(+)
and g.CID =d.CID(+)
and g.CID =e.CID(+)
and g.CID =f.CID(+)
and g.CNAME=a.CNAME(+)
and g.CNAME=b.CNAME(+)
and g.CNAME=c.CNAME(+)
and g.CNAME=d.CNAME(+)
and g.CNAME=e.CNAME(+)
and g.CNAME=f.CNAME(+)
and g.TYPE=a.TYPE(+)
and g.TYPE=b.TYPE(+)
and g.TYPE=c.TYPE(+)
and g.TYPE=d.TYPE(+)
and g.TYPE=e.TYPE(+)
and g.TYPE=f.TYPE(+)
你的SQL语句应该是如下的写法,但是结果有9条记录,而且很难去掉有重复字段的记录.select g.TNAME,
g.CID ,
g.CNAME ,
g.TYPE,
a.PK,
b.UK,
c.GK,
d.CHK_NAME,
e.CHK_VAL,
f.NULLABLE
from
(
select TNAME, CID , CNAME ,TYPE,PK
from t_struct a1
where a1.PK is not null
group by TNAME, CID , CNAME ,TYPE,PK
) a,(
select TNAME, CID , CNAME ,TYPE,UK
from t_struct b1
where b1.uK is not null
group by TNAME, CID , CNAME ,TYPE,UK
) b,
(
select TNAME, CID , CNAME ,TYPE,GK
from t_struct c1
where c1.GK is not null
group by TNAME, CID , CNAME ,TYPE,GK
) c,
(
select TNAME, CID , CNAME ,TYPE,CHK_NAME
from t_struct d1
where d1.CHK_NAME is not null
group by TNAME, CID , CNAME ,TYPE,CHK_NAME
) d,
(
select TNAME, CID , CNAME ,TYPE,CHK_VAL
from t_struct e1
where e1.CHK_VAL is not null
group by TNAME, CID , CNAME ,TYPE,CHK_VAL
) e,
(
select TNAME, CID , CNAME ,TYPE,NULLABLE
from t_struct f1
where f1.NULLABLE is not null
group by TNAME, CID , CNAME ,TYPE,NULLABLE
) f,
(
select TNAME, CID , CNAME ,TYPE,NULLABLE
from t_struct g1
where g1.NULLABLE is not null
group by TNAME, CID , CNAME ,TYPE,NULLABLE
) g
where
g.TNAME=a.TNAME(+)
and g.TNAME=b.TNAME(+)
and g.TNAME=c.TNAME(+)
and g.TNAME=d.TNAME(+)
and g.TNAME=e.TNAME(+)
and g.TNAME=f.TNAME(+)
and g.CID =a.CID(+)
and g.CID =b.CID(+)
and g.CID =c.CID(+)
and g.CID =d.CID(+)
and g.CID =e.CID(+)
and g.CID =f.CID(+)
and g.CNAME=a.CNAME(+)
and g.CNAME=b.CNAME(+)
and g.CNAME=c.CNAME(+)
and g.CNAME=d.CNAME(+)
and g.CNAME=e.CNAME(+)
and g.CNAME=f.CNAME(+)
and g.TYPE=a.TYPE(+)
and g.TYPE=b.TYPE(+)
and g.TYPE=c.TYPE(+)
and g.TYPE=d.TYPE(+)
and g.TYPE=e.TYPE(+)
and g.TYPE=f.TYPE(+)