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'
参与人数 = (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)
我把你的改成mysql了,但是数据结果完全对不上
;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
自己改一下相对应的列名。
我用的是mysql数据库,我改了对应的列名,执行报错
参考一下这个贴子的提问方式http://bbs.csdn.net/topics/320211382
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。