1>未读情况列表
create proc dbo.proc_unreadlist(@fileid varchar(10))
as
begin
set nocount on
select a.* from user a where a.userid not in(
select b.userid from send b where not exists(select 1 from read c
where b.userid=c.userid and b.fileid=c.fileid))
end
create proc dbo.proc_unreadlist(@fileid varchar(10))
as
begin
set nocount on
select a.* from user a where a.userid not in(
select b.userid from send b where not exists(select 1 from read c
where b.userid=c.userid and b.fileid=c.fileid))
end
create proc dbo.proc_unreadlist(@fileid varchar(10))
as
begin
set nocount on
select a.* from user a where a.userid not in(
select b.userid from send b where not exists(select 1 from read c
where b.userid=c.userid and b.fileid=c.fileid) and b.fileid=@fileid)
end
2>已读情况
create proc dbo.proc_unreadlist(@fileid varchar(10))
as
begin
set nocount on
select b.userid,b.fileid,b.username,count(1) as [读次数],sum(datediff(ss,readinttime,readouttime)) as 总计读的时间 from (select a.*,c.username from read a left join user c on a.userid=c.userid) b where b.fileid=@fileid group by b.userid,b.fileid,b.usernameend