例如:
表ABC:
id num createTime
1 5 2010-8-6 18:53:29
2 9 2010-8-10 18:53:29
3 10 2010-8-30 18:53:29
4 2 2010-9-1 18:53:29
5 1 2010-9-26 18:53:29画面入力:
开始日:2010-8-01
结束日:2010-9-30
用Sql如何能求出每个月的num总和。也就是2010-8-01到2010-8-30的num的总和,2010-9-01到2010-9-30的num的总和
表ABC:
id num createTime
1 5 2010-8-6 18:53:29
2 9 2010-8-10 18:53:29
3 10 2010-8-30 18:53:29
4 2 2010-9-1 18:53:29
5 1 2010-9-26 18:53:29画面入力:
开始日:2010-8-01
结束日:2010-9-30
用Sql如何能求出每个月的num总和。也就是2010-8-01到2010-8-30的num的总和,2010-9-01到2010-9-30的num的总和
select to_char(createTime,'yyyy-mm'),sum(num) total
from abc group by to_char(createTime,'yyyy-mm')
FROM abc
GROUP BY TO_CHAR(createTime,'YYYY-MM');
FROM ABC
GROUP BY TO_CHAR(createTime, 'yyyy-mm');
开始日:2010-8-01
结束日:2010-9-30如何设定creatTime
from abc
group by trunc(createTime,'mm');先把createtime截取到月或者转换到月,再分组就可以了
FROM ABC
GROUP BY TO_CHAR(createTime, 'yyyy-mm'); -- 將createTime轉換成YYYY-MM的格式,然後分組計算
FROM ABC
WHERE createTime between to_date('2010-08-01','yyyy-mm-dd') and to_date('2010-09-01','yyyy-mm-dd')
GROUP BY TO_CHAR(createTime, 'yyyy-mm'); -- 將createTime轉換成YYYY-MM的格式,然後分組計算
from abc
如何将开始日和结束日设定到sql中
from abc
where createTime between 开始日 and 结束日
group by to_char(createTime,'yyyy-mm')
改为to_date行不?