本文共 1722 字,大约阅读时间需要 5 分钟。
”本月 102“, 这是错的你少加了“10 7 41 2009-1-5 16:26:39”这条数据!
结果具体如下:
SQL> select A_ID, PAYMONEY, to_char(A_TIME, 'YYYY-MM-DD HH24:MI:SS') A_TIME from aa;
A_ID PAYMONEY A_TIME
---------- ---------- -------------------
1 11 2009-01-07 16:38:21
2 12 2009-01-07 16:38:21
3 13 2009-01-07 16:38:21
4 21 2009-01-06 16:38:21
5 22 2009-01-06 16:38:21
6 23 2009-01-06 16:38:21
7 31 2008-12-31 16:38:21
8 32 2008-12-31 16:38:22
9 33 2008-12-31 16:38:22
7 41 2009-01-05 16:38:22
8 52 2008-12-08 16:38:22
9 53 2008-12-08 16:38:22
12 rows selected
SQL>
SQL> select tmp2.st,
2 decode(tmp2.st,
3 '今日',
4 tmp1.sum_1,
5 '昨日',
6 tmp1.sum_2,
7 '上周',
8 tmp1.sum_3,
9 '本月',
10 tmp1.sum_4,
11 '本年',
12 tmp1.sum_5,
13 null) "交易总额"
14 from
15 (select sum(decode(a1, '今日', PAYMONEY, null)) sum_1,
16 sum(decode(a2, '昨日', PAYMONEY, null)) sum_2,
17 sum(decode(a3, '上周', PAYMONEY, null)) sum_3,
18 sum(decode(a4, '本月', PAYMONEY, null)) sum_4,
19 sum(decode(a5, '本年', PAYMONEY, null)) sum_5
20 from
21 (select A_ID,
22 PAYMONEY,
23 to_char(A_TIME, 'YYYY-MM-DD HH24:MI:SS') A_TIME,
24 decode(trunc(A_TIME, 'DD'), trunc(sysdate, 'DD'), '今日', null) a1,
25 decode(trunc(A_TIME, 'DD'), trunc(sysdate - 1, 'DD'), '昨日', null) a2,
26 decode(trunc(A_TIME, 'DD'), trunc(sysdate - 7, 'DD'), '上周', null) a3,
27 decode(to_char(A_TIME, 'YYYYMM'), to_char(sysdate, 'YYYYMM'), '本月', null) a4,
28 decode(to_char(A_TIME, 'YYYY'), to_char(sysdate, 'YYYY'), '本年', null) a5
29 from aa)) tmp1,
30 (select '今日' st from dual
31 union all
32 select '昨日' from dual
33 union all
34 select '上周' from dual
35 union all
36 select '本月' from dual
37 union all
38 select '本年' from dual
39 ) tmp2;
ST 交易总额
---- ----------
今日 36
昨日 66
上周 96
本月 143
本年 143
SQL>
[本帖最后由 bell6248 于 2009-1-7 17:11 编辑]
转载地址:https://blog.csdn.net/weixin_33744799/article/details/116323303 如侵犯您的版权,请留言回复原文章的地址,我们会给您删除此文章,给您带来不便请您谅解!