SQL如何根据输入的年份统计这一年每月的销售金额

create table sale

(id int,

name varchar(10),

sdate datetime,

money int)

insert into sale values (1,'西瓜','2015-01-01',10)

insert into sale values (2,'香蕉','2015-01-05',20)

insert into sale values (3,'苹果','2015-02-01',60)

insert into sale values (4,'葡萄','2015-02-23',345)

insert into sale values (5,'柚子','2015-04-23',10)

insert into sale values (6,'牛奶','2015-05-12',67)

insert into sale values (7,'地瓜','2015-06-01',10)

insert into sale values (8,'土豆','2015-07-01',10)

insert into sale values (9,'土豆','2016-07-01',10)

如何根据我自己要查找的年份来显示每月的金额 ，如果某个月销售为0，就显示为0

SELECT SUM(money),MONTH(sdate) AS s FROM sale WHERE YEAR(sdate) = ? GROUP BY MONTH(sdate)

如果你的某个月的金额为0已经填写到sale表里,下面的语句是统计每个月的金额的sql语句
SELECT TOP 500 sum(money) as '每月统计金额'
,MONTH(sdate) as '月份'
FROM [Test].[dbo].[sale]
where YEAR(sdate)='2016 '
group by MONTH(sdate)
否则的话你可以建一个临时表,先生成每个月的月份的一列,然后给定初始值为0,然后对应把值插入到临时表中

额,,没有 top 500 ,这个是我粘错了的

select month(sdate) as 月份,SUM(money) as 销售额 from sale where year(sdate)=2015 group by month(sdate)

select a.MONTH(sdate) as '月份', a.money as '每月金额'
from (select * from sale where to_char(sdate, 'yyyy') = ?) a
group by MONTH(sdate)

如果你的要求是列出某一年每个月销售额（没有销售记录的月份也出现）
下面的第一个子查询select distinct name from sale是用于如果你想查看每一个产品每一个月的销售时才需要(再group by中增加g.name)，否则直接主表是spt_number

 select sv.number as [Month],sum(isnull(money,0)) as SaledAmount
from (select distinct name from sale) g
inner join master.dbo.spt_values as sv on sv.type='P' and sv.number between 1 and 12
left join sale on sale.name=g.name and year(sdate)=2016 and month(sdate)=sv.number
group by sv.number
order by sv.number

+-------+-------------+
| Month | SaledAmount |
+-------+-------------+
| 1 | 0 |
| 2 | 0 |
| 3 | 0 |
| 4 | 0 |
| 5 | 0 |
| 6 | 0 |
| 7 | 10 |
| 8 | 0 |
| 9 | 0 |
| 10 | 0 |
| 11 | 0 |
| 12 | 0 |
+-------+-------------+