我想得到1-12月的价格合计!
select sum(price)as price,month(SellDate)as SellMonth from sells where month(SellDate)=1
union
select sum(price)as price,month(SellDate)as SellMonth from sells where month(SellDate)=2
union
select sum(price)as price,month(SellDate)as SellMonth from sells where month(SellDate)=3
union
select sum(price)as price,month(SellDate)as SellMonth from sells where month(SellDate)=4
union
select sum(price)as price,month(SellDate)as SellMonth from sells where month(SellDate)=5
union
select sum(price)as price,month(SellDate)as SellMonth from sells where month(SellDate)=6
union
select sum(price)as price,month(SellDate)as SellMonth from sells where month(SellDate)=7
union
select sum(price)as price,month(SellDate)as SellMonth from sells where month(SellDate)=8
union
select sum(price)as price,month(SellDate)as SellMonth from sells where month(SellDate)=9
union
select sum(price)as price,month(SellDate)as SellMonth from sells where month(SellDate)=10
union
select sum(price)as price,month(SellDate)as SellMonth from sells where month(SellDate)=11
union
select sum(price)as price,month(SellDate)as SellMonth from sells where month(SellDate)=12如何优化此SQL语句!
select sum(price)as price,month(SellDate)as SellMonth from sells where month(SellDate)=1
union
select sum(price)as price,month(SellDate)as SellMonth from sells where month(SellDate)=2
union
select sum(price)as price,month(SellDate)as SellMonth from sells where month(SellDate)=3
union
select sum(price)as price,month(SellDate)as SellMonth from sells where month(SellDate)=4
union
select sum(price)as price,month(SellDate)as SellMonth from sells where month(SellDate)=5
union
select sum(price)as price,month(SellDate)as SellMonth from sells where month(SellDate)=6
union
select sum(price)as price,month(SellDate)as SellMonth from sells where month(SellDate)=7
union
select sum(price)as price,month(SellDate)as SellMonth from sells where month(SellDate)=8
union
select sum(price)as price,month(SellDate)as SellMonth from sells where month(SellDate)=9
union
select sum(price)as price,month(SellDate)as SellMonth from sells where month(SellDate)=10
union
select sum(price)as price,month(SellDate)as SellMonth from sells where month(SellDate)=11
union
select sum(price)as price,month(SellDate)as SellMonth from sells where month(SellDate)=12如何优化此SQL语句!
where month(SellDate)>=1 and month(SellDate)<=12
Group by month(SellDate)
也不对!
如果我6-12月没有数据的话,数据就不出来了。
where datediff(yy,selldate,getdate())=0
Group by month(SellDate)
order by month(SellDate)
还有给selldate 加个索引 这样做SUM求和时速度快点
sum(price)as price,
month(SellDate)as SellMonth
from
sells
group by
month(SellDate)
a.SellMonth,isnull(b.price,0) as price
from
(select 1 as SellMonth union select 2 union ... union select 12) a
left join
(select sum(price)as price,month(SellDate)as SellMonth from sells group by month(SellDate)) b
on
a.SellMonth=b.SellMonth
我的原意是就是表里没有7月份的数据也返会出7月份,不过数据是null
也就是说执行这条SQL,一定要返回12行数据!
只是把这个isnull(b.price,0),直接用b.price显示就可以了