表结构如下
user表:
name(主键),emailmessage表:
fromUser(引用user表name),
toUser(引用user表name)实现
当fromName=name 输出toUser的email
当toName=name 输出fromName的email
user表:
name(主键),emailmessage表:
fromUser(引用user表name),
toUser(引用user表name)实现
当fromName=name 输出toUser的email
当toName=name 输出fromName的email
select email from user inner join message
on user.name = message.toUser and fromUser = name1;2.
select email from user inner join message
on user.name = message.fromUser and toUser = name2;
select name,
(select email from message b where b.fromUser= a.name) from_email,
(select email from message b where b.toUser= a.name) to_email
from user a 另外不明白这样做的意义,难道to的邮件接收人就只能是一个吗?不能有多个吗?
b.name
,b.email --fromName 邮箱
,c.name --toName 邮箱
,c.email
from message a
inner join user b
on a.fromUser=b.name
inner join user c
and a.toName=c.name
;
何必不换个表结构
这样的表结构有问题
select name, email from user where exists(
select toUser
from user,message
where user.name = message.fromUser
union
select fromUser
from user,message
where user.name=message.toUser
)
当toName=name 输出fromName的email其实就是输出所有name的email:
select a.email from user a,message b where a.name=b.fromUser
union all
select a.email from user a,message b where a.name=b.toUser
name email
A [email protected]
B [email protected]
C [email protected] toUser
A B
C B
比如上面的这组数据,按照楼主的意思,应该只需要输出A和C的吧?
如果fromUser=name的话,就不判断toUser了吧?
不知道楼主是不是这个意思