表normaltran
trandate tranamount
2008-01-01 50
2008-01-15 80
2008-02-13 45
2009-05-15 60
2009-05-16 35
2009-05-25 66
想得到最终结果为
trandate tranamount
2008-01 140
2008-02 45
2008-05 95
。。。。。。。。。。
请问sql语句该怎么写呀
ä½¿ç¨ case when é å sumæ¥ç»è®¡ã
å¦å¾è¡¨a2
è¯å¥å¦ä¸ï¼sumé¨å大æï¼å½æ¶é´å¨åºé´å slåå SUM计ç®ï¼å¦åsl以0åå SUM计ç®
select以åæ¹å¼ç»è®¡ï¼æ´ä¸ºç®åï¼ærq 转æ¢ä¸ºå¹´+æ æ ¼å¼ï¼ååç»å³å¯ä»¥ã
select year(rq) * 100 + month(rq) as rq, sum(sl) as 'sl'如果表中还有一列mchntid
1
2
3
。。。。。。
结果:
1 200801 140
2 200802 45
mchntid 写在哪?
这有个问题:2008年1月有两条记录,选择记录的mchntid 呢?
如果选择每组最小的mchntid ,可以用聚合函数 min(mchntid).
如下:
select min(mchntid) as mchntid , year(trandate) * 100 + month(trandate) as trandate, sum(tranamount) as tranamount
from normaltran
group by year(trandate) * 100 + month(trandate)