表名: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,