急！！！请各位大神帮忙看看。

[yang_lawrence]

select accountName,accountId,
(select count(logId) from syslog where objectTypeId=2010 and eventTypeId=2 and syslog.accountId=sysaccount.accountId and syslog.createTime between '2012-12-28 00:00:00' and '2013-01-28 23:59:59')as addresource,
(select count(logId) from syslog where objectTypeId=2010 and eventTypeId=4 and syslog.accountId=sysaccount.accountId and syslog.createTime between '2012-12-28 00:00:00' and '2013-01-28 23:59:59')as updateresource,
(select count(logId) from syslog where objectTypeId=2010 and eventTypeId=3 and syslog.accountId=sysaccount.accountId and syslog.createTime between '2012-12-28 00:00:00' and '2013-01-28 23:59:59')as deleteresource,
(select count(logId) from syslog where objectTypeId=2015 and eventTypeId=4 and syslog.accountId=sysaccount.accountId and syslog.createTime between '2012-12-28 00:00:00' and '2013-01-28 23:59:59')as updatejianli,
(select count(logId) from syslog where objectTypeId=2010 and eventTypeId=5 and syslog.accountId=sysaccount.accountId and syslog.createTime between '2012-12-28 00:00:00' and '2013-01-28 23:59:59')as lookresource,
(select count(logId) from syslog where objectTypeId=2010 and eventTypeId=6 and syslog.accountId=sysaccount.accountId and syslog.createTime between '2012-12-28 00:00:00' and '2013-01-28 23:59:59')as shangchuanresourcefujian,
(select count(logId) from syslog where objectTypeId=2010 and eventTypeId=7 and syslog.accountId=sysaccount.accountId and syslog.createTime between '2012-12-28 00:00:00' and '2013-01-28 23:59:59')as xiazairesourcefujian,
(select count(logId) from syslog where objectTypeId=2010 and eventTypeId=8 and syslog.accountId=sysaccount.accountId and syslog.createTime between '2012-12-28 00:00:00' and '2013-01-28 23:59:59')as shuanchuresourcefujian,
(select count(logId) from syslog where objectTypeId=2030 and eventTypeId=2 and syslog.accountId=sysaccount.accountId and syslog.createTime between '2012-12-28 00:00:00' and '2013-01-28 23:59:59')as addresourcepingyu,
(select count(logId) from syslog where objectTypeId=2040 and eventTypeId=4 and syslog.accountId=sysaccount.accountId and syslog.createTime between '2012-12-28 00:00:00' and '2013-01-28 23:59:59')as udpateresourcebeijingdiaocha,
(select count(logId) from syslog where objectTypeId=2050 and eventTypeId=4 and syslog.accountId=sysaccount.accountId and syslog.createTime between '2012-12-28 00:00:00' and '2013-01-28 23:59:59')as lookresourcemingganxinxi,
(select count(logId) from syslog where objectTypeId=2050 and eventTypeId=5 and syslog.accountId=sysaccount.accountId and syslog.createTime between '2012-12-28 00:00:00' and '2013-01-28 23:59:59')as shenpiresourcemingganxinxi,
(select count(logId) from syslog where objectTypeId=2080 and eventTypeId=2 and syslog.accountId=sysaccount.accountId and syslog.createTime between '2012-12-28 00:00:00' and '2013-01-28 23:59:59')as addresourcexunfang,
(select count(logId) from syslog where objectTypeId=2080 and eventTypeId=4 and syslog.accountId=sysaccount.accountId and syslog.createTime between '2012-12-28 00:00:00' and '2013-01-28 23:59:59')as updateresourcexunfang,
(select count(logId) from syslog where objectTypeId=2080 and eventTypeId=3 and syslog.accountId=sysaccount.accountId and syslog.createTime between '2012-12-28 00:00:00' and '2013-01-28 23:59:59')as shanchuresourcexunfang,
(select count(logId) from syslog where objectTypeId=2090 and eventTypeId=2 and syslog.accountId=sysaccount.accountId and syslog.createTime between '2012-12-28 00:00:00' and '2013-01-28 23:59:59')as addresourcexunfangpingyu,
(select COUNT(Distinct resCommentsId) from rescomments rt where (select createAccId from resource where resourceId=rt.resourceId)!= rt.commentAccId and rt.createTime between '2012-12-28 00:00:00' and '2013-01-28 23:59:59' and rt.commentAccId=sysaccount.accountId)as addxunfangpingyufeibenren,
(select count(logId) from syslog where objectTypeId=20100 and eventTypeId=2 and syslog.accountId=sysaccount.accountId and syslog.createTime between '2012-12-28 00:00:00' and '2013-01-28 23:59:59')as addmubiaogongsi,
(select count(logId) from syslog where objectTypeId=20100 and eventTypeId=4 and syslog.accountId=sysaccount.accountId and syslog.createTime between '2012-12-28 00:00:00' and '2013-01-28 23:59:59')as updatemubiaogongsi,
(select count(logId) from syslog where objectTypeId=20100 and eventTypeId=3 and syslog.accountId=sysaccount.accountId and syslog.createTime between '2012-12-28 00:00:00' and '2013-01-28 23:59:59')as shanchumubiaogongsi,
(select count(logId) from syslog where objectTypeId=20110 and eventTypeId=2 and syslog.accountId=sysaccount.accountId and syslog.createTime between '2012-12-28 00:00:00' and '2013-01-28 23:59:59')as shangchuanmobiaogongsifujian,
(select count(logId) from syslog where objectTypeId=20110 and eventTypeId=6 and syslog.accountId=sysaccount.accountId and syslog.createTime between '2012-12-28 00:00:00' and '2013-01-28 23:59:59')as xiazaimubiaogongsifujian,
(select count(logId) from syslog where objectTypeId=20100 and eventTypeId=7 and syslog.accountId=sysaccount.accountId and syslog.createTime between '2012-12-28 00:00:00' and '2013-01-28 23:59:59')as shanchumobiaogongsifujian,
(select count(logId) from syslog where objectTypeId=820100 and eventTypeId=2 and syslog.accountId=sysaccount.accountId and syslog.createTime between '2012-12-28 00:00:00' and '2013-01-28 23:59:59')as zhuanhuaxianren,
(select count(logId) from syslog where objectTypeId=820100 and eventTypeId=4 and syslog.accountId=sysaccount.accountId and syslog.createTime between '2012-12-28 00:00:00' and '2013-01-28 23:59:59')as upatexianren,
(select count(logId) from syslog where objectTypeId=820100 and eventTypeId=3 and syslog.accountId=sysaccount.accountId and syslog.createTime between '2012-12-28 00:00:00' and '2013-01-28 23:59:59')as shanchuxianren,
(select count(logId) from syslog where objectTypeId=820110 and eventTypeId=2 and syslog.accountId=sysaccount.accountId and syslog.createTime between '2012-12-28 00:00:00' and '2013-01-28 23:59:59')as shenqingzhuanhuaxianren,
(select count(logId) from syslog where objectTypeId=820110 and eventTypeId=4 and syslog.accountId=sysaccount.accountId and syslog.createTime between '2012-12-28 00:00:00' and '2013-01-28 23:59:59')as pizhunzhuanhuaxianren,
(select count(logId) from syslog where objectTypeId=820110 and eventTypeId=3 and syslog.accountId=sysaccount.accountId and syslog.createTime between '2012-12-28 00:00:00' and '2013-01-28 23:59:59')as jujuezhuanhuaxianrenshenqin
from dbo.sysaccount where status not in(2,3) order by accountName asc


请各位大神帮忙看看这个查询有什么问题？查询半天都不出结果。急求！！！

[yang_lawrence]

引用 2 楼 Haiwer 的回复:

select sysaccount.accountName,sysaccount.accountId, sum(case when syslog.objectTypeId=2010 and syslog.eventTypeId=2 and syslog.createTime between '2012-12-28 00:00:00' and '2013-01-28 23……

但是用你的方法也是很慢的。

[昵称被占用了]

这么多子查询当然慢了

[昵称被占用了]

select sysaccount.accountName,sysaccount.accountId, sum(case when syslog.objectTypeId=2010 and syslog.eventTypeId=2 and syslog.createTime between '2012-12-28 00:00:00' and '2013-01-28 23:59:59' then 1 else 0 end) as addresource, sum(case when syslog.objectTypeId=2010 and syslog.eventTypeId=4 and syslog.createTime between '2012-12-28 00:00:00' and '2013-01-28 23:59:59' then 1 else 0 end) as updateresource, ... from sysaccount ,syslog where sysaccount.status not in(2,3) and syslog.accountId=sysaccount.accountId group by sysaccount.accountName,sysaccount.accountId order by sysaccount.accountName asc

[sd722522]

你这不慢就怪了