關于多表查詢的一個題
sql腳本如下
生成表及關系的腳本：
/*==============================================================*/
/* DBMS name:????? Microsoft SQL Server 2000??????????????????? */
/* Created on:???? 2007-7-11 12:48:23?????????????????????????? */
/*==============================================================*/
alter table WORKS
?? drop constraint FK_WORKS_REFERENCE_AGE
go
alter table WORKS
?? drop constraint FK_WORKS_REFERENCE_FAVERATE
go
alter table WORKS
?? drop constraint FK_WORKS_REFERENCE_ROLE
go
alter table rights
?? drop constraint FK_RIGHTS_REFERENCE_ROLE
go
alter table rights
?? drop constraint FK_RIGHTS_REFERENCE_USERS
go
alter table users
?? drop constraint FK_USERS_REFERENCE_FAVERATE
go
if exists (select 1
??????????? from? sysobjects
?????????? where? id = object_id('WORKS')
??????????? and?? type = 'U')
?? drop table WORKS
go
if exists (select 1
??????????? from? sysobjects
?????????? where? id = object_id('age')
??????????? and?? type = 'U')
?? drop table age
go
if exists (select 1
??????????? from? sysobjects
?????????? where? id = object_id('faverate')
??????????? and?? type = 'U')
?? drop table faverate
go
if exists (select 1
??????????? from? sysobjects
?????????? where? id = object_id('rights')
??????????? and?? type = 'U')
?? drop table rights
go
if exists (select 1
??????????? from? sysobjects
?????????? where? id = object_id('role')
??????????? and?? type = 'U')
?? drop table role
go
if exists (select 1
??????????? from? sysobjects
?????????? where? id = object_id('users')
??????????? and?? type = 'U')
?? drop table users
go
/*==============================================================*/
/* Table: WORKS???????????????????????????????????????????????? */
/*==============================================================*/
create table WORKS (
?? id?????????????????? int????????????????? not null,
?? name???????????????? char(1)????????????? null,
?? age????????????????? int????????????????? null,
?? faverate???????????? int????????????????? null,
?? role???????????????? int????????????????? null,
?? constraint PK_WORKS primary key? (id)
)
go
execute sp_addextendedproperty 'MS_Description',
?? '工作表',
?? 'user', '', 'table', 'WORKS'
go
/*==============================================================*/
/* Table: age?????????????????????????????????????????????????? */
/*==============================================================*/
create table age (
?? id?????????????????? int????????????????? not null,
?? type???????????????? char(1)????????????? null,
?? span???????????????? int????????????????? null,
?? constraint PK_AGE primary key? (id)
)
go
execute sp_addextendedproperty 'MS_Description',
?? '年齡表',
?? 'user', '', 'table', 'age'
go
/*==============================================================*/
/* Table: faverate????????????????????????????????????????????? */
/*==============================================================*/
create table faverate (
?? id?????????????????? int????????????????? not null,
?? type???????????????? char(1)????????????? null,
?? description????????? char(1)????????????? null,
?? constraint PK_FAVERATE primary key? (id)
)
go
execute sp_addextendedproperty 'MS_Description',
?? '愛好表',
?? 'user', '', 'table', 'faverate'
go
/*==============================================================*/
/* Table: rights??????????????????????????????????????????????? */
/*==============================================================*/
create table rights (
?? id?????????????????? int????????????????? not null,
?? uid????????????????? int????????????????? null,
?? rid????????????????? int????????????????? null,
?? constraint PK_RIGHTS primary key? (id)
)
go
execute sp_addextendedproperty 'MS_Description',
?? '權限表',
?? 'user', '', 'table', 'rights'
go
/*==============================================================*/
/* Table: role????????????????????????????????????????????????? */
/*==============================================================*/
create table role (
?? id?????????????????? int????????????????? not null,
?? name???????????????? char(1)????????????? null,
?? constraint PK_ROLE primary key? (id)
)
go
execute sp_addextendedproperty 'MS_Description',
?? '角色表',
?? 'user', '', 'table', 'role'
go
/*==============================================================*/
/* Table: users???????????????????????????????????????????????? */
/*==============================================================*/
create table users (
?? id?????????????????? int????????????????? not null,
?? name???????????????? char(1)????????????? null,
?? age????????????????? int????????????????? null,
?? faverate???????????? int????????????????? null,
?? sex????????????????? char(1)????????????? null,
?? constraint PK_USERS primary key? (id)
)
go
execute sp_addextendedproperty 'MS_Description',
?? '用戶表',
?? 'user', '', 'table', 'users'
go
alter table WORKS
?? add constraint FK_WORKS_REFERENCE_AGE foreign key (age)
????? references age (id)
go
alter table WORKS
?? add constraint FK_WORKS_REFERENCE_FAVERATE foreign key (faverate)
????? references faverate (id)
go
alter table WORKS
?? add constraint FK_WORKS_REFERENCE_ROLE foreign key (role)
????? references role (id)
go
alter table rights
?? add constraint FK_RIGHTS_REFERENCE_ROLE foreign key (rid)
????? references role (id)
go
alter table rights
?? add constraint FK_RIGHTS_REFERENCE_USERS foreign key (id)
????? references users (id)
go
alter table users
?? add constraint FK_USERS_REFERENCE_FAVERATE foreign key (faverate)
????? references faverate (id)
go
查詢腳本 ：
select * from AGE
select * from FAVERATE
select * from RIGHTS
select * from ROLE
select * from USERS
select u.id as uid,u.name as uname,u.age as uage,u.faverate as ufaver,r.rid as rrid from USERS u,RIGHTS r where r.uid=uid
?
select wid,age,faverate,role from WORKS
select u.id as uid,u.age as uage,u.faverate as ufaver,r.rid as rrid from USERS u,RIGHTS r where r.uid=uid
?
<--符合條件的工作-->
select distinct w.wid,a.span as aspan,w.faverate as wfaver,v.uid,v.uage,v.ufaver from AGE a,WORKS w,
?(select u.id as uid,u.age as uage,u.faverate as ufaver,r.rid as rrid from USERS u,RIGHTS r
??where r.uid=uid) v
where a.ageid=w.age and a.span=uage and w.faverate=v.ufaver
?
<--不符合條件的工作-->
select users.name 人名,works.name 工作名,v2.aspan 需求年齡,v2.uage 用戶年齡,v2.wfaver 需求愛好,v2.ufaver 用戶愛好 from users,works,
(select distinct w.wid,a.span as aspan,w.faverate as wfaver,v.uid,v.uage,v.ufaver from AGE a,WORKS w,
?(select u.id as uid,u.age as uage,u.faverate as ufaver,r.rid as rrid from USERS u,RIGHTS r
??where r.uid=uid) v
??????? where a.ageid=w.age and (a.span!=uage or w.faverate!=v.ufaver )) v2
where v2.wid=works.wid and v2.uid=users.id order by users.name
?