56,678
社区成员
发帖
与我相关
我的任务
分享
;with sel as(select q.useid as quseid,r.useid as ruseid,convert(varchar(8),q.instaltime,112)
as qinstaltime,convert(varchar(8),q.instaltime,112)as rinstaltime
from question q join reply r
on a.id=r.quesid and q.useid=r.useid and convert(varchar(8),q.instaltime,112)=
convert(varchar(8),q.instaltime,112)
)
select qinstaltime,count(quseid)+count(ruseid) as total,
(count(quseid)+count(ruseid))/(select count(id) from information) as percen,
count(quseid),count(ruseid) from sel
group by qinstaltime
自己改一下相对应的列名。select 时间=convert(varchar(10),q.instaltime,120),
参与人数 = (select COUNT(distinct useid) from Reply where quesId=q.id),
参与比例 = (select COUNT(distinct useid) from Reply where quesId=q.id)/(select COUNT(1) from Information)+'%',
发帖数 = (select COUNT(1) from Question where convert(varchar(10),instaltime,120)=convert(varchar(10),q.instaltime,120)),
回帖数 = (select COUNT(1) from Reply where convert(varchar(10),instaltime,120)=convert(varchar(10),q.instaltime,120))
from Question q
group by convert(varchar(10),q.instaltime,120)
select 时间=q.instaltime,
参与人数 = (select COUNT(1) from Information where id=q.useId or id=r.useId),
参与比例 = (select COUNT(1) from Information where id=q.useId or id=r.useId)/(select COUNT(1) from Information)+'%',
发帖数 = (select COUNT(1) from Question where id=q.id),
回帖数 = (select COUNT(1) from Reply where quesId=q.id)
from Question q
INNER JOIN Information i
ON q.useId=i.id
INNER JOIN Reply r
ON r.quesId=q.id
where q.instaltime='2013-05-12'