数据表:
create table BBS_Users
(
userName varchar2(20) primary key,
passWord varchar(20) not null,
email varchar2(50) not null
);
insert into BBS_Users values('admin','admin','[email protected]');
insert into BBS_Users values('manager','manager','[email protected]');create table BBS_Messages
(
messId number(4) primary key,
toName varchar2(20) not null,
fromName varchar2(20) not null,
title varchar2(100) not null,
content varchar2(400) not null,
pastTime timesTamp not null,
constraint FK_messages_from foreign key(fromName) references BBS_Users(userName),
constraint FK_messages_to foreign key(toName) references BBS_Users(userName)
);
insert into BBS_Messages values(1,'admin','waghito','aaaaaa','aaaaaaaaaa',to_date('2012-2-14 12:32:29','yyyy-mm-dd hh24:mi:ss'));
insert into BBS_Messages values(2,'waghito','admin','bbbbbb','bbbbbbbbbb',to_date('2012-2-14 12:40:29','yyyy-mm-dd hh24:mi:ss'));实现
当toName=userName 输出user.userName,message.fromName
当fromName=userName 输出user.userName,messsage.toName求教育
create table BBS_Users
(
userName varchar2(20) primary key,
passWord varchar(20) not null,
email varchar2(50) not null
);
insert into BBS_Users values('admin','admin','[email protected]');
insert into BBS_Users values('manager','manager','[email protected]');create table BBS_Messages
(
messId number(4) primary key,
toName varchar2(20) not null,
fromName varchar2(20) not null,
title varchar2(100) not null,
content varchar2(400) not null,
pastTime timesTamp not null,
constraint FK_messages_from foreign key(fromName) references BBS_Users(userName),
constraint FK_messages_to foreign key(toName) references BBS_Users(userName)
);
insert into BBS_Messages values(1,'admin','waghito','aaaaaa','aaaaaaaaaa',to_date('2012-2-14 12:32:29','yyyy-mm-dd hh24:mi:ss'));
insert into BBS_Messages values(2,'waghito','admin','bbbbbb','bbbbbbbbbb',to_date('2012-2-14 12:40:29','yyyy-mm-dd hh24:mi:ss'));实现
当toName=userName 输出user.userName,message.fromName
当fromName=userName 输出user.userName,messsage.toName求教育
insert into BBS_Messages values(2,'waghito','admin','bbbbbb','bbbbbbbbbb',to_date('2012-2-14 12:40:29','yyyy-mm-dd hh24:mi:ss'));--------------------
外键'waghito' 确保BBS_Users 列userName 存在键'waghito' 。select bu.userName,bm.fromName from BBS_Users bu inner join BBS_Messages bm
on bu.userName=bm.toName where bm.toName='userName'select bu.userName,bm.toName from BBS_Users bu inner join BBS_Messages bm
on bu.userName=bm.fromName where bm.fromName='userName'
select bu.userName,bm.fromName from BBS_Users bu inner join BBS_Messages bm
on bu.userName=bm.toName
union
select bu.userName,bm.toName from BBS_Users bu inner join BBS_Messages bm
on bu.userName=bm.fromName
insert into BBS_Messages values(1,'admin','waghito','aaaaaa','aaaaaaaaaa',to_date('2012-2-14 12:32:29','yyyy-mm-dd hh24:mi:ss'));
insert into BBS_Messages values(2,'waghito','admin','bbbbbb','bbbbbbbbbb',to_date('2012-2-14 12:40:29','yyyy-mm-dd hh24:mi:ss'));
这两条insert语句就不可能插入得进去,你这两表存在外键约束 而依赖表根本就不存在'waghito'这用户。
因为有外键约束
实现
当toName=userName 输出user.userName,message.fromName---这时候user.userName=toName
当fromName=userName 输出user.userName,messsage.toName---这时候user.userName=fromName
select toName,fromName from BBS_Messages where toName='xxxxx' or fromName='xxxxx'
就可以解决你的需求
我打个比方
我要获取的是所有和userName(admin)有关的数据
假如Messages的fromName字段是admin 则输出Messages表的toName字段和toName的email
假如Messages的toName字段是admin 则输出Messages表的fromName字段和fromName的email
where userName=(select toName from BBS_messages where fromName='xxxx' )
or userName=(select fromName from BBS_Messages where toName='xxxx');
userName=(select toName from BBS_messages where fromName='xxxx' )
和
userName=(select fromName from BBS_Messages where toName='xxxx');
改成
userName in (select toName from BBS_messages where fromName='xxxx' )
和
userName in (select fromName from BBS_Messages where toName='xxxx');
BU.email
From BBS_Messages BM INNER JOIN BBS_Users BU
ON DECODE(BM.fromName,'xxxx',BM.TOName,BM.fromName) = BU.userName
WHERE (BM.fromName='xxxx' OR BM.TOName='xxxx' )