关于返回多个sum计算值的效率问题.
表名:k_DayInfo
字段:ZJC,JLR 数据类型为float
过滤条件:DateAndTime 数据类型为Datetime
实现的功能:返回表 k_DayInfo 今日/3日/5日/10日/15日/20日/30日/35日/40日/50日 字段ZJC,JLR的和
现我用以下两个语句都可实现,但是执行效率太差.大家还有没有更好的办法? 效率最快都将送上70分.谢谢
语句一:
SELECT top 1
(sum(a.ZJC)/10000) AS ZJC,
(sum(a.JLR)) AS JLR,
(sum(b.ZJC)/10000) AS ZJC3,
(sum(b.JLR)) AS JLR3,
(sum(c.ZJC)/10000) AS ZJC5,
(sum(c.JLR)) AS JLR5,
(sum(d.ZJC)/10000) AS ZJC10,
(sum(d.JLR)) AS JLR10,
(sum(e.ZJC)/10000) AS ZJC15,
(sum(e.JLR)) AS JLR15,
(sum(f.ZJC)/10000) AS ZJC20,
(sum(f.JLR)) AS JLR20,
(sum(g.ZJC)/10000) AS ZJC25,
(sum(g.JLR)) AS JLR25,
(sum(h.ZJC)/10000) AS ZJC30,
(sum(h.JLR)) AS JLR30,
(sum(i.ZJC)/10000) AS ZJC35,
(sum(i.JLR)) AS JLR35,
(sum(j.ZJC)/10000) AS ZJC40,
(sum(j.JLR)) AS JLR40,
(sum(k.ZJC)/10000) AS ZJC50,
(sum(k.JLR)) AS JLR50
FROM k_DayInfo a,k_DayInfo b,k_DayInfo c,k_DayInfo d,k_DayInfo e,k_DayInfo f,k_DayInfo g,k_DayInfo h,k_DayInfo i
WHERE
a.CID = 213 AND DATEDIFF(day, a.DateAndTime, '2007-10-16 0:00:00') <= 0
and b.CID = a.CID AND DATEDIFF(day, b.DateAndTime, '2007-10-12 0:00:00') <= 0
and c.CID = a.CID AND DATEDIFF(day, c.DateAndTime, '2007-10-10 0:00:00') <= 0
and d.CID = a.CID AND DATEDIFF(day, d.DateAndTime, '2007-9-25 0:00:00') <= 0
and e.CID = a.CID AND DATEDIFF(day, e.DateAndTime, '2007-9-18 0:00:00') <= 0
and f.CID = a.CID AND DATEDIFF(day, f.DateAndTime, '2007-9-11 0:00:00') <= 0
and g.CID = a.CID AND DATEDIFF(day, g.DateAndTime, '2007-9-4 0:00:00') <= 0
and h.CID = a.CID AND DATEDIFF(day, h.DateAndTime, '2007-8-28 0:00:00') <= 0
and i.CID = a.CID AND DATEDIFF(day, i.DateAndTime, '2007-8-21 0:00:00') <= 0
and j.CID = a.CID AND DATEDIFF(day, j.DateAndTime, '2007-8-14 0:00:00') <= 0
and k.CID = a.CID AND DATEDIFF(day, k.DateAndTime, '2007-8-14 0:00:00') <= 0
语句二:
SELECT top 1
(sum(a.ZJC)/10000) AS ZJC,
(sum(a.JLR)) AS JLR,
(sum(b.ZJC)/10000) AS ZJC3,
(sum(b.JLR)) AS JLR3,
(SELECT sum(ZJC)/10000 FROM k_DayInfo WHERE CID = 213 AND (DATEDIFF(day, DateAndTime, '2007-10-10 0:00:00') <= 0)) AS ZJC5,
(SELECT sum(JLR) FROM k_DayInfo WHERE CID = 213 AND (DATEDIFF(day, DateAndTime, '2007-10-10 0:00:00') <= 0)) AS JLR5,
(SELECT sum(ZJC)/10000 FROM k_DayInfo WHERE CID = 213 AND (DATEDIFF(day, DateAndTime, '2007-9-25 0:00:00') <= 0)) AS ZJC10,
(SELECT sum(JLR) FROM k_DayInfo WHERE CID = 213 AND (DATEDIFF(day, DateAndTime, '2007-9-25 0:00:00') <= 0)) AS JLR10,
(SELECT sum(ZJC)/10000 FROM k_DayInfo WHERE CID = 213 AND (DATEDIFF(day, DateAndTime, '2007-9-18 0:00:00') <= 0)) AS ZJC15,
(SELECT sum(JLR) FROM k_DayInfo WHERE CID = 213 AND (DATEDIFF(day, DateAndTime, '2007-9-18 0:00:00') <= 0)) AS JLR15,
(SELECT sum(ZJC)/10000 FROM k_DayInfo WHERE CID = 213 AND (DATEDIFF(day, DateAndTime, '2007-9-11 0:00:00') <= 0)) AS ZJC20,
(SELECT sum(JLR) FROM k_DayInfo WHERE CID = 213 AND (DATEDIFF(day, DateAndTime, '2007-9-11 0:00:00') <= 0)) AS JLR20,
(SELECT sum(ZJC)/10000 FROM k_DayInfo WHERE CID = 213 AND (DATEDIFF(day, DateAndTime, '2007-9-4 0:00:00') <= 0)) AS ZJC25,
(SELECT sum(JLR) FROM k_DayInfo WHERE CID = 213 AND (DATEDIFF(day, DateAndTime, '2007-9-4 0:00:00') <= 0)) AS JLR25,
(SELECT sum(ZJC)/10000 FROM k_DayInfo WHERE CID = 213 AND (DATEDIFF(day, DateAndTime, '2007-8-28 0:00:00') <= 0)) AS ZJC30, (SELECT sum(JLR) FROM k_DayInfo WHERE CID = 213 AND (DATEDIFF(day, DateAndTime, '2007-8-28 0:00:00') <= 0)) AS JLR30,
(SELECT sum(ZJC)/10000 FROM k_DayInfo WHERE CID = 213 AND (DATEDIFF(day, DateAndTime, '2007-8-21 0:00:00') <= 0)) AS ZJC35,
(SELECT sum(JLR) FROM k_DayInfo WHERE CID = 213 AND (DATEDIFF(day, DateAndTime, '2007-8-21 0:00:00') <= 0)) AS JLR35,
(SELECT sum(ZJC)/10000 FROM k_DayInfo WHERE CID = 213 AND (DATEDIFF(day, DateAndTime, '2007-8-14 0:00:00') <= 0)) AS ZJC40,
(SELECT sum(JLR) FROM k_DayInfo WHERE CID = 213 AND (DATEDIFF(day, DateAndTime, '2007-8-14 0:00:00') <= 0)) AS JLR40,
(SELECT sum(ZJC)/10000 FROM k_DayInfo WHERE CID = 213 AND (DATEDIFF(day, DateAndTime, '2007-8-14 0:00:00') <= 0)) AS ZJC50,
(SELECT sum(JLR) FROM k_DayInfo WHERE CID = 213 AND (DATEDIFF(day, DateAndTime, '2007-8-14 0:00:00') <= 0)) AS JLR50
[解决办法]
这种多重连接表提速的一般办法是用临时表或者表变量.
[解决办法]
select * from
(SELECT (sum(a.ZJC)/10000) AS ZJC,(sum(a.JLR)) AS JLR from k_DayInfo
where CID=213 ) a
cross join
(SELECT (sum(a.ZJC)/10000) AS ZJC3,(sum(a.JLR)) AS JLR3 from k_DayInfo
where CID=213 and datediff(day,DateAndTime,getdate())=3) b
cross join
(SELECT (sum(a.ZJC)/10000) AS ZJC5,(sum(a.JLR)) AS JLR5 from k_DayInfo
where CID=213 and datediff(day,DateAndTime,getdate())=5) c
cross join
(SELECT (sum(a.ZJC)/10000) AS ZJC10,(sum(a.JLR)) AS JLR10 from k_DayInfo
where CID=213 and datediff(day,DateAndTime,getdate())=10) d
cross join
(SELECT (sum(a.ZJC)/10000) AS ZJC15,(sum(a.JLR)) AS JLR15 from k_DayInfo
where CID=213 and datediff(day,DateAndTime,getdate())=15) e
cross join
(SELECT (sum(a.ZJC)/10000) AS ZJC20,(sum(a.JLR)) AS JLR20 from k_DayInfo
where CID=213 and datediff(day,DateAndTime,getdate())=20) f
cross join
(SELECT (sum(a.ZJC)/10000) AS ZJC25,(sum(a.JLR)) AS JLR25 from k_DayInfo
where CID=213 and datediff(day,DateAndTime,getdate())=25) g
cross join
(SELECT (sum(a.ZJC)/10000) AS ZJC30,(sum(a.JLR)) AS JLR30 from k_DayInfo
where CID=213 and datediff(day,DateAndTime,getdate())=30) h
cross join
(SELECT (sum(a.ZJC)/10000) AS ZJC35,(sum(a.JLR)) AS JLR35 from k_DayInfo
where CID=213 and datediff(day,DateAndTime,getdate())=35) i
cross join
(SELECT (sum(a.ZJC)/10000) AS ZJC40,(sum(a.JLR)) AS JLR40 from k_DayInfo
where CID=213 and datediff(day,DateAndTime,getdate())=40) j
cross join
(SELECT (sum(a.ZJC)/10000) AS ZJC50,(sum(a.JLR)) AS JLR50 from k_DayInfo
where CID=213 and datediff(day,DateAndTime,getdate())=50) k
