按月统计销售额SQL语句请教

[fangsky]

ACCESS中的记录样式是：
编号 名称 日期(odate) 金额(Omoney) 备注
1 苹果 2014/1/30 200
2 草莓 2014/1/30 100
3 香蕉 2014/2/30 210
4 香蕉 2014/3/30 550
...
想得到结果样式：
年 月 金额
2014 1 310
2014 2 210
2014 3 550
...
请问有什么SQL语句能实现，谢谢！
我用这个语句能得到金额和月份。但是没有同时获取到年份。
select sum(Omoney),month(odate) as lwmonth from Torder group by month(odate)

[Dogfish]

select 
sum(Omoney) as Omoney,
year(odate) as yy,
month(odate) as mm
from yourtable
group by year(odate),month(odate)
order by year(odate),month(odate)

[疯狂的豆子c]

SELECT 1,1,b.*,c.* FROM (SELECT 1,SUM(worth) AS outworth, SUM(IF(SUBSTRING(createtime,6,2)='01',worth,0)) AS jan, SUM(IF(SUBSTRING(createtime,6,2)='02',worth,0)) AS feb, SUM(IF(SUBSTRING(createtime,6,2)='03',worth,0)) AS mar, SUM(IF(SUBSTRING(createtime,6,2)='04',worth,0)) AS apr, SUM(IF(SUBSTRING(createtime,6,2)='05',worth,0)) AS may, SUM(IF(SUBSTRING(createtime,6,2)='06',worth,0)) AS june, SUM(IF(SUBSTRING(createtime,6,2)='07',worth,0)) AS july, SUM(IF(SUBSTRING(createtime,6,2)='08',worth,0)) AS aug, SUM(IF(SUBSTRING(createtime,6,2)='09',worth,0)) AS sep, SUM(IF(SUBSTRING(createtime,6,2)='10',worth,0)) AS octo, SUM(IF(SUBSTRING(createtime,6,2)='11',worth,0)) AS nov, SUM(IF(SUBSTRING(createtime,6,2)='12',worth,0)) AS dece FROM storage_out WHERE STATUS=1 AND createtime LIKE '2013%' AND employee_id IN (SELECT id FROM employee WHERE department IN (10000))) b LEFT JOIN(SELECT 1,SUM(worth) AS returnworth, SUM(IF(SUBSTRING(createtime,6,2)='01',worth,0)) AS jan, SUM(IF(SUBSTRING(createtime,6,2)='02',worth,0)) AS feb, SUM(IF(SUBSTRING(createtime,6,2)='03',worth,0)) AS mar, SUM(IF(SUBSTRING(createtime,6,2)='04',worth,0)) AS apr, SUM(IF(SUBSTRING(createtime,6,2)='05',worth,0)) AS may, SUM(IF(SUBSTRING(createtime,6,2)='06',worth,0)) AS june, SUM(IF(SUBSTRING(createtime,6,2)='07',worth,0)) AS july, SUM(IF(SUBSTRING(createtime,6,2)='08',worth,0)) AS aug, SUM(IF(SUBSTRING(createtime,6,2)='09',worth,0)) AS sep, SUM(IF(SUBSTRING(createtime,6,2)='10',worth,0)) AS octo, SUM(IF(SUBSTRING(createtime,6,2)='11',worth,0)) AS nov, SUM(IF(SUBSTRING(createtime,6,2)='12',worth,0)) AS dece FROM storage_return WHERE STATUS=1 AND createtime LIKE '2013%' AND employee_id IN (SELECT id FROM employee WHERE department IN (10000)))c ON 1=1 稍作加工应该可以用，希望对你有用

[dgxs34]

select  sum(Omoney) as Omoney, year(odate) as yy, month(odate) as mm from yourtable group by year(odate),month(odate) order by year(odate),month(odate)

[fangsky]

引用 1 楼 dogfish 的回复:

select 
sum(Omoney) as Omoney,
year(odate) as yy,
month(odate) as mm
from yourtable
group by year(odate),month(odate)
order by year(odate),month(odate)

这样不行year(odate) 这个加入后，报错了。

[Dogfish]

引用 4 楼 fangsky 的回复:

[quote=引用 1 楼 dogfish 的回复:]

select 
sum(Omoney) as Omoney,
year(odate) as yy,
month(odate) as mm
from yourtable
group by year(odate),month(odate)
order by year(odate),month(odate)

这样不行year(odate) 这个加入后，报错了。 [/quote] 我很少使用access。楼主你看看是否有相对应的函数。