select * from 表名 a where a.交易时间>=to_char(TRUNC(SYSDATE, 'mm'),'yyyy-mm-dd') and a.交易时间<=to_char(last_day(SYSDATE),'yyyy-mm-dd')
SQL> with tab as 2 ( 3 select sysdate-10 adate,1001 acctcode,'存款' transType,1000 amount from dual union all 4 select sysdate-11 adate,1001 acctcode,'存款' transType,1000 amount from dual union all 5 select sysdate-12 adate,1001 acctcode,'存款' transType,1000 amount from dual union all 6 select sysdate-13 adate,1001 acctcode,'取款' transType,1000 amount from dual union all 7 select sysdate-14 adate,1001 acctcode,'存款' transType,1000 amount from dual union all 8 select sysdate-50 adate,1001 acctcode,'取款' transType,1000 amount from dual union all 9 select sysdate-51 adate,1001 acctcode,'存款' transType,1000 amount from dual 10 ) 11 select trunc(adate,'mm'), 12 acctcode, 13 sum(decode(transType, '存款', amount, -amount)) 14 from tab 15 group by trunc(adate,'mm'), acctcode 16 ;TRUNC(ADATE,'MM') ACCTCODE SUM(DECODE(TRANSTYPE,'存款',AM ----------------- ---------- ------------------------------ 2011-2-1 1001 3000 2010-12-1 1001 0SQL>
and a.交易时间<=to_char(last_day(SYSDATE),'yyyy-mm-dd')
SQL> with tab as
2 (
3 select sysdate-10 adate,1001 acctcode,'存款' transType,1000 amount from dual union all
4 select sysdate-11 adate,1001 acctcode,'存款' transType,1000 amount from dual union all
5 select sysdate-12 adate,1001 acctcode,'存款' transType,1000 amount from dual union all
6 select sysdate-13 adate,1001 acctcode,'取款' transType,1000 amount from dual union all
7 select sysdate-14 adate,1001 acctcode,'存款' transType,1000 amount from dual union all
8 select sysdate-50 adate,1001 acctcode,'取款' transType,1000 amount from dual union all
9 select sysdate-51 adate,1001 acctcode,'存款' transType,1000 amount from dual
10 )
11 select trunc(adate,'mm'),
12 acctcode,
13 sum(decode(transType, '存款', amount, -amount))
14 from tab
15 group by trunc(adate,'mm'), acctcode
16 ;TRUNC(ADATE,'MM') ACCTCODE SUM(DECODE(TRANSTYPE,'存款',AM
----------------- ---------- ------------------------------
2011-2-1 1001 3000
2010-12-1 1001 0SQL>