select sum(money) from tb t1, (select min(thetime) min_dt from tb) t2 where t1.thetime between t2.min_dt and last_day(t2.min_dt)
select sum(money) from tb t1, (select min(thetime) min_dt from tb) t2 where t1.thetime between t2.min_dt and last_day(t2.min_dt) 如果时间字段带小时分秒的话这么写有点问题,最后一天可能有部分统计不进来
SELECT SUM(MONEY) totalmoney FROM T WHERE TO_CHAR(thetime,'YYYYMM')=(SELECT TO_CHAR(MIN(thetime),'YYYYMM') FROM T)这个SQL 只能统计一条数据 ,不能统计 26 日至 30 日之和 啊 。。
每天一条数据?加个group by就行了 SELECT TO_CHAR(thetime,'YYYY-MM-DD'),SUM(MONEY) totalmoney FROM T WHERE TO_CHAR(thetime,'YYYYMM')=(SELECT TO_CHAR(MIN(thetime),'YYYYMM') FROM T) group by TO_CHAR(thetime,'YYYY-MM-DD') ORDER BY TO_CHAR(thetime,'YYYY-MM-DD')
select sum(money) from tb t1, (select min(thetime) min_dt from tb group by to_char(thetime,'yyyymm')) t2 where t1.thetime between t2.min_dt and last_day(t2.min_dt) ;
from tb t1, (select min(thetime) min_dt from tb) t2
where t1.thetime between t2.min_dt and last_day(t2.min_dt)
from tb t1, (select min(thetime) min_dt from tb) t2
where t1.thetime between t2.min_dt and last_day(t2.min_dt)
如果时间字段带小时分秒的话这么写有点问题,最后一天可能有部分统计不进来
FROM T
WHERE TO_CHAR(thetime,'YYYYMM')=(SELECT TO_CHAR(MIN(thetime),'YYYYMM') FROM T)这个SQL 只能统计一条数据 ,不能统计 26 日至 30 日之和 啊 。。
SELECT TO_CHAR(thetime,'YYYY-MM-DD'),SUM(MONEY) totalmoney
FROM T
WHERE TO_CHAR(thetime,'YYYYMM')=(SELECT TO_CHAR(MIN(thetime),'YYYYMM') FROM T)
group by TO_CHAR(thetime,'YYYY-MM-DD')
ORDER BY TO_CHAR(thetime,'YYYY-MM-DD')
版主好,发现个问题 ,如果 thetime 中 包含 6月 26 到 8月 27 之间的数据,现在我需要 按月 进行 统计,就是 结果 要分成3个部分 ,
6月份的 SUM(MONEY) totalmoney 7月份的 SUM(MONEY) totalmoney 8月份的 SUM(MONEY) totalmoney 分成三份 求 版主帮忙 !
select sum(money)
from tb t1, (select min(thetime) min_dt from tb group by to_char(thetime,'yyyymm')) t2
where t1.thetime between t2.min_dt and last_day(t2.min_dt) ;