SQL> select * from bill;
BILLID BILLDATE SEATID SUM VIP PAYED MADE BILLUUID BILLCHECK
---------------------------------------- -------------- ------- --------- ---- ----- ---- -------- ---------
20111108137 20111108103947 9 1.0 2 1 137 2
20111108139 20111108104009 4 1.0 2 2 139 2
20111108141 20111108104024 7 1.0 2 2 141 2
20111108143 20111108104326 7 1.0 2 2 143 2
20111109172 20111109090803 5 1.0 1 2 172 1
20111110192 20111110124946 10 1.0 2 2 192 2
20111101193 20111101125024 1 1.0 2 2 193 2
20111030194 20111030125108 2 1.0 2 2 194 2
20110915195 20110915125128 3 1.0 2 2 195 2
20110901196 20110901125142 4 1.0 2 2 196 2
20110922197 20110922125156 5 1.0 2 2 197 2
20110928198 20110928125210 1 1.0 2 2 198 2
20111006199 20111006125230 2 1.0 2 2 199 2
20111013200 20111013125243 3 1.0 2 2 200 2
20111027201 20111027125256 4 1.0 2 2 201 2
20111103202 20111103125316 5 1.0 2 2 202 2
20111108132 20111108101744 1 6.0 1.0 1 2 132 1
20111108133 20111108101746 2 21.0 1.0 2 2 133 1
20111109134 20111109101750 3 12.0 1.0 2 2 134 1
20111108135 20111108101754 4 1.0 2 1 135 2
BILLID BILLDATE SEATID SUM VIP PAYED MADE BILLUUID BILLCHECK
---------------------------------------- -------------- ------- --------- ---- ----- ---- -------- ---------
20111109173 20111109141248 5 1.0 2 2 173 2
20111130203 20111130125341 6 1.0 2 2 203 2
20111207204 20111207125352 7 1.0 2 2 204 2
怎么将上面的数据按查询单位:周、月、年查询统计出来(只用查在单位时间内的sum之和,也就是sum(sum));
BILLID BILLDATE SEATID SUM VIP PAYED MADE BILLUUID BILLCHECK
---------------------------------------- -------------- ------- --------- ---- ----- ---- -------- ---------
20111108137 20111108103947 9 1.0 2 1 137 2
20111108139 20111108104009 4 1.0 2 2 139 2
20111108141 20111108104024 7 1.0 2 2 141 2
20111108143 20111108104326 7 1.0 2 2 143 2
20111109172 20111109090803 5 1.0 1 2 172 1
20111110192 20111110124946 10 1.0 2 2 192 2
20111101193 20111101125024 1 1.0 2 2 193 2
20111030194 20111030125108 2 1.0 2 2 194 2
20110915195 20110915125128 3 1.0 2 2 195 2
20110901196 20110901125142 4 1.0 2 2 196 2
20110922197 20110922125156 5 1.0 2 2 197 2
20110928198 20110928125210 1 1.0 2 2 198 2
20111006199 20111006125230 2 1.0 2 2 199 2
20111013200 20111013125243 3 1.0 2 2 200 2
20111027201 20111027125256 4 1.0 2 2 201 2
20111103202 20111103125316 5 1.0 2 2 202 2
20111108132 20111108101744 1 6.0 1.0 1 2 132 1
20111108133 20111108101746 2 21.0 1.0 2 2 133 1
20111109134 20111109101750 3 12.0 1.0 2 2 134 1
20111108135 20111108101754 4 1.0 2 1 135 2
BILLID BILLDATE SEATID SUM VIP PAYED MADE BILLUUID BILLCHECK
---------------------------------------- -------------- ------- --------- ---- ----- ---- -------- ---------
20111109173 20111109141248 5 1.0 2 2 173 2
20111130203 20111130125341 6 1.0 2 2 203 2
20111207204 20111207125352 7 1.0 2 2 204 2
怎么将上面的数据按查询单位:周、月、年查询统计出来(只用查在单位时间内的sum之和,也就是sum(sum));
to_char(sysdate,'yyyymmddHH24MISS')
下面是一个简单例子:
SQL> select job,deptno,sal from emp;
JOB DEPTNO SAL
--------- --------- ---------
CLERK 20 800
SALESMAN 30 1600
SALESMAN 30 1250
MANAGER 20 2975
SALESMAN 30 1250
MANAGER 30 2850
MANAGER 10 2450
ANALYST 20 3000
PRESIDENT 10 5000
SALESMAN 30 1500
CLERK 20 1100
CLERK 30 950
ANALYST 20 3000
CLERK 10 1300
已选择14行。
SQL> select job,deptno,sum(sal) total_sal from emp group by rollup(job,deptno);
JOB DEPTNO TOTAL_SAL
--------- --------- ---------
ANALYST 20 6000
ANALYST 6000
CLERK 10 1300
CLERK 20 1900
CLERK 30 950
CLERK 4150
MANAGER 10 2450
MANAGER 20 2975
MANAGER 30 2850
MANAGER 8275
PRESIDENT 10 5000
PRESIDENT 5000
SALESMAN 30 5600
SALESMAN 5600
29025
SELECT TO_CHAR(BillDate), SUM(sum) from Bill GROUP BY TO_CHAR(BillDate, 'ww')
2.按照每月进行统计
SELECT TO_CHAR(BillDate), SUM(sum) from Bill GROUP BY TO_CHAR(BillDate, 'mm')
3.按照每季度进行统计
SELECT TO_CHAR(BillDate), SUM(sum) from Bill GROUP BY TO_CHAR(BillDate, 'q')
4.按照每年进行统计
SELECT TO_CHAR(BillDate), SUM(sum) from Bill GROUP BY TO_CHAR(BillDate, 'YYYY')