Ora92@Ly>CREATE TABLE ys_dkb06(rq date, dwdm number, xlmc varchar2(20), hdl number(8));表已创建。已用时间: 00: 00: 00.00
Ora92@Ly>select * from ys_dkb06;RQ DWDM XLMC HDL
---------- ---------- -------------------- ----------
03-6月 -05 12 wwe 1
03-6月 -05 12 rrr 2
03-6月 -05 10 iie 3
02-6月 -05 12 wwe 10
02-6月 -05 12 rrr 20
02-6月 -05 10 iie 30
01-6月 -05 12 wwe 2
01-6月 -05 12 rrr 3
01-6月 -05 10 iid 5已选择9行。已用时间: 00: 00: 00.00
Ora92@Ly>SELECT RQ,
2 SUM(HDL) AS 总数,
3 SUM(SUM(HDL)) OVER(ORDER BY RQ ROWS UNBOUNDED PRECEDING) AS 累计总数
4 FROM YS_DKB06
5 GROUP BY RQ;RQ 总数 累计总数
---------- ---------- ----------
01-6月 -05 10 10
02-6月 -05 60 70
03-6月 -05 6 76已用时间: 00: 00: 00.00
Ora92@Ly>
Ora92@Ly>select * from ys_dkb06;RQ DWDM XLMC HDL
---------- ---------- -------------------- ----------
03-6月 -05 12 wwe 1
03-6月 -05 12 rrr 2
03-6月 -05 10 iie 3
02-6月 -05 12 wwe 10
02-6月 -05 12 rrr 20
02-6月 -05 10 iie 30
01-6月 -05 12 wwe 2
01-6月 -05 12 rrr 3
01-6月 -05 10 iid 5已选择9行。已用时间: 00: 00: 00.00
Ora92@Ly>SELECT RQ,
2 SUM(HDL) AS 总数,
3 SUM(SUM(HDL)) OVER(ORDER BY RQ ROWS UNBOUNDED PRECEDING) AS 累计总数
4 FROM YS_DKB06
5 GROUP BY RQ;RQ 总数 累计总数
---------- ---------- ----------
01-6月 -05 10 10
02-6月 -05 60 70
03-6月 -05 6 76已用时间: 00: 00: 00.00
Ora92@Ly>
SQL> SELECT RQ,
2 SUM(HDL) AS 今日hdl,
3 LAG(SUM(HDL)) OVER(ORDER BY RQ) AS 昨日,
4 SUM(SUM(HDL)) OVER(ORDER BY RQ) AS 月累计hdl
5 FROM YS_DKB06
6 GROUP BY RQ
7 ORDER BY RQ DESC;RQ 今日HDL 昨日 月累计HDL
---------- ---------- ---------- ----------
2005-06-03 6 60 76
2005-06-02 60 10 70
2005-06-01 10 10
如:
2005-5-3 10 iie 3
2005-5-2 12 wwe 10
2005-5-2 12 rrr 20
2005-5-2 10 iie 30
2005-6-1 12 wwe 2
2005-6-1 12 rrr 3
2005-6-3 12 wwe 1
2005-6-3 12 rrr 2
2005-6-3 10 iie 3
2005-6-2 12 wwe 10
2005-6-2 12 rrr 20
2005-6-2 10 iie 30
2005-6-1 12 wwe 2
2005-6-1 12 rrr 3
2005-6-1 10 iie 5
每月只要累计本月的数据,请教怎样写?
2 SUM(HDL) AS 今日hdl,
3 LAG(SUM(HDL)) OVER(ORDER BY RQ) AS 昨日,
4 SUM(SUM(HDL)) OVER(ORDER BY RQ) AS 月累计hdl
5 FROM YS_DKB06
6 WHERE RQ>=(SELECT TO_DATE('2005-06-01','YYYY-MM-DD') FROM DUAL)
7 GROUP BY RQ
8 ORDER BY RQ DESC;RQ 今日HDL 昨日 月累计HDL
---------- ---------- ---------- ----------
2005-06-03 6 60 76
2005-06-02 60 10 70
2005-06-01 10 10
SQL> SELECT RQ,
2 SUM(HDL) AS 今日hdl,
3 LAG(SUM(HDL)) OVER(ORDER BY RQ) AS 昨日,
4 SUM(SUM(HDL)) OVER(ORDER BY RQ) AS 月累计hdl
5 FROM YS_DKB06
6 WHERE RQ>=(SELECT TO_DATE('2005-06-01','YYYY-MM-DD') FROM DUAL)
7 AND
8 RQ<(SELECT TO_DATE('2005-07-01','YYYY-MM-DD') FROM DUAL)
9 GROUP BY RQ
10 ORDER BY RQ DESC;RQ 今日HDL 昨日 月累计HDL
---------- ---------- ---------- ----------
2005-06-03 6 60 76
2005-06-02 60 10 70
2005-06-01 10 10
---------- ---------- -------------------- ----------
2005-05-03 10 wwe 3
2005-05-03 12 rrr 10
2005-05-02 10 iie 20
2005-05-02 12 wwe 30
2005-06-03 11 wwe 1
2005-06-03 21 rrr 2
2005-06-03 31 iie 3
2005-06-02 10 wwe 10
2005-06-02 20 wwe 20
2005-06-02 30 rrr 30
2005-06-01 21 iie 2RQ DWDM XLMC HDL
---------- ---------- -------------------- ----------
2005-06-01 31 wwe 3
2005-06-01 51 wwe 5已选择13行。SQL> SELECT RQ,
2 SUM(HDL) AS 今日hdl,
3 LAG(SUM(HDL)) OVER(partition by trunc(rq,'mm') ORDER BY RQ) AS 昨日,
4 SUM(SUM(HDL)) OVER(partition by trunc(rq,'mm') ORDER BY RQ) AS 月累计hdl
5 FROM YS_DKB06
6 GROUP BY RQ
7 ORDER BY RQ DESC;RQ 今日HDL 昨日 月累计HDL
---------- ---------- ---------- ----------
2005-06-03 6 60 76
2005-06-02 60 10 70
2005-06-01 10 10
2005-05-03 13 50 63
2005-05-02 50 50