本人有一表
user 表 用户表
userid
username
send 表 文章发送表
fileid 文章id
userid 发送至用户的id(一个文章发送至多个userid)
read 表 文章阅读表
readid 阅读id
fileid 文章id
userid 用户阅读的userid
readintime 阅读开始时间 datatime
readouttime 阅读退出时间 datatime
每个用户看一次 记录一个记录
要求 选出 某 fileid 文章 的用户阅读情况
未读情况列表
userid,username
已读情况
userid,username,读次数，总计读的时间(秒)
------解决方案--------------------
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
------解决方案--------------------
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) and [email protected])
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 [email protected] group by b.userid,b.fileid,b.username
end