各位大哥,麻烦看一下!

Question

这个语句
select distinct  users.nickname,visit, users.userid,users.username,users.logintimes,users.regdate,
(select distinct count(blog.userid) from blog where blog.userid=users.userid) blog,
(select distinct count(reply.userid) from reply where reply.userid=users.userid) reply
,datediff(day,regdate,getdate()) days
from users,blog,reply在查询分析器里居然要2'40''秒,我郁闷的很
那位大哥麻烦看一下(user,blog,reply只有不到2000条记录)

Answer 1

distinct 可以用GROUP BY放到下面那样应该快一点

Answer 2

from users,blog,reply --後面應該有條件的, 沒有的話就會造成交叉聯接

Answer 3

select users.nickname,visit, users.userid,users.username,users.logintimes,users.regdate,
[blog]=count(blog.userid),[reply]=count(reply.userid)
from users,blog,reply
where blog.userid=users.userid and reply.userid=users.userid
group by users.nickname,visit, users.userid,users.username,users.logintimes,users.regdate

Answer 4

select users.nickname,visit, users.userid,users.username,users.logintimes,users.regdate,
[blog]=count(blog.userid),[reply]=count(reply.userid),[days]=datediff(day,regdate,getdate())--这里最好不要用getdate函数，指定时间这样会快点
from users,blog,reply
where blog.userid=users.userid and reply.userid=users.userid
group by users.nickname,visit, users.userid,users.username,users.logintimes,users.regdate

Answer 5

这样改看看如果有错误请把visit和regdate加上什么点，如U.visit
SELECT U.nickname,visit, U.userid,U.username,U.logintimes,U.regdate,
    B.blog, R.reply, DATEDIFF(DAY,regdate,getdate()) AS days
FROM USERS U
LEFT JOIN(SELECT USERID, COUNT(1) AS blog FROM blog GROUP BY USERID)B
ON U.USERID = B.USERID
LEFT JOIN(SELECT USERID, COUNT(1) AS reply FROM reply)R
ON U.USEID = R.USERID