select pms.pmsid,pms.msgfromid,pms.msgtoid,pms.isnew,pms.title,pms.message,pms.senddate,[user].username,commonsms.hide
from pms inner join [user] on pms.msgfromid = [user].userid inner join commonsms on commonsms.pmsid = pms.pmsid where commonsms.userid = 11111
union all
select pms.pmsid,pms.msgfromid,pms.msgtoid,pms.isnew,pms.title,pms.message,pms.senddate,[user].username,pms.hide
from pms inner join [user] on pms.msgfromid = [user].userid
where pms.pmsid not in(select commonsms.pmsid from commonsms where commonsms.userid = 11111) and pms.sort = 1
from pms inner join [user] on pms.msgfromid = [user].userid inner join commonsms on commonsms.pmsid = pms.pmsid where commonsms.userid = 11111
union all
select pms.pmsid,pms.msgfromid,pms.msgtoid,pms.isnew,pms.title,pms.message,pms.senddate,[user].username,pms.hide
from pms inner join [user] on pms.msgfromid = [user].userid
where pms.pmsid not in(select commonsms.pmsid from commonsms where commonsms.userid = 11111) and pms.sort = 1
from pms inner join [user] on pms.msgfromid = [user].userid inner join commonsms on commonsms.pmsid = pms.pmsid where commonsms.userid = 11111
union all
select pms.pmsid,pms.msgfromid,pms.msgtoid,pms.isnew,pms.title,pms.message,pms.senddate,[user].username,pms.hide
from pms inner join [user] on pms.msgfromid = [user].userid
where pms.pmsid not in(select commonsms.pmsid from commonsms where commonsms.userid = 11111) and pms.sort = 1
CREATE TABLE [dbo].[pms] (
[pmsid] [int] IDENTITY (1, 1) NOT NULL ,
[msgfromid] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[msgtoid] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[isnew] [int] NULL ,
[title] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[message] [ntext] COLLATE Chinese_PRC_CI_AS NULL ,
[senddate] [datetime] NULL ,
[sort] [int] NULL ,
[msgfromhide] [int] NULL ,
[msgtohide] [int] NULL ,
[hide] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
CREATE TABLE [dbo].[commonsms] (
[commonsmsid] [int] IDENTITY (1, 1) NOT NULL ,
[pmsid] [int] NOT NULL ,
[userid] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[hide] [int] NULL
) ON [PRIMARY]
user表就是一个userid跟一个username就不给出来了 谢谢大家!麻烦了~
短信表
[pmsid] 短信id
[msgfromid] 发信人的userid
[msgtoid] 收信人的userid
[isnew] 是否看过
[title] 短信标题
[message] 短信内容
[senddate] 发送时间
[sort] 短信类型
[msgfromhide] 发信人是否隐藏此短信
[msgtohide] 收信人是否隐藏此短信
[hide] (union all 必须要求列一致 此列无意义)公共短信表
[commonsmsid] 公共短信id
[pmsid] [int]短信id
[userid] userid
[hide] 是否隐藏此短信
from pms inner join [user] on pms.msgfromid = [user].userid inner join commonsms on commonsms.pmsid = pms.pmsid where commonsms.userid = 11111
or pms.sort = 1
from pms,[user],commonsms
where pms.msgfromid = [user].userid and commonsms.userid = 11111
and (commonsms.pmsid = pms.pmsid or commonsms.pmsid != pms.pmsid and pms.sort = 1)