msg表:消息表
msg_id number(20),
grp_id number(20).
msg varchar(256)
)
recv_users表:消息接收者
grp_id number(20),
user_id number(8)msg_recvs表--消息已读表(无记录为未读,有记录且read_flag=1为已读)
msg_id number(20),
user_id number(8)
read_flag number(1)想得到用户已读与未读消息情况
---原来想这么实现---可是表连接只能一个所以就实现不了。请大侠们想想办法。
select a.msg_id,a.msg,c.read_flag from msg a,recv_users b,msg_recvs c
where a.grp_id=b.grp_id and b.user_id=c.user_id(+) and a.msg_id=c.msg_id(+)
select a.msg_id,a.msg,nvl(c.read_flag,0) as read_flag
from msg a Inner Join recv_users b on a.grp_id=b.grp_id
Left Join msg_recvs c on b.user_id=c.user_id
and a.msg_id=c.msg_id
) b
where a.msg_id (+)=b.msg_id and a.grp_id (+)=b.grp_id
order by read_flag desc
from msg a left Join recv_users b on a.grp_id=b.grp_id
Left Join msg_recvs c on b.user_id=c.user_id and a.msg_id=c.msg_id
from msg_recvs c,
(select a.msg_id,a.grp_id,a.msg,b.user_id
from msg a,recv_users b
where a.grp_id=b.grp_id ) e
where e.user_id=c.user_id(+) read_flag=0 未读 =1为已读