关于多表查询的一个题
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