oracle中group by按月分組統計

創建測試表

我們提供的服務有：網站設計制作、網站設計、微信公眾號開發、網站優化、網站認證、于洪ssl等。為成百上千企事業單位解決了網站和推廣的問題。提供周到的售前咨詢和貼心的售后服務，是有科學管理、有技術的于洪網站制作公司

create?table?test

(姓名?varchar2(10),

訪問時間?date,

進入時間?date,

離開時間?date);

insert?into?test?values?('張三',to_date('2013-02-03?15:23:22','yyyy-mm-dd?hh24:mi:ss'),to_date('2013-02-03?15:23:22','yyyy-mm-dd?hh24:mi:ss'),to_date('2013-02-03?15:33:22','yyyy-mm-dd?hh24:mi:ss'));

insert?into?test?values?('李四',to_date('2013-02-04?15:23:22','yyyy-mm-dd?hh24:mi:ss'),to_date('2013-02-04?18:23:22','yyyy-mm-dd?hh24:mi:ss'),to_date('2013-02-04?18:53:22','yyyy-mm-dd?hh24:mi:ss'));

insert?into?test?values?('王武',to_date('2013-02-04?15:23:22','yyyy-mm-dd?hh24:mi:ss'),to_date('2013-02-05?17:23:22','yyyy-mm-dd?hh24:mi:ss'),to_date('2013-02-05?18:23:22','yyyy-mm-dd?hh24:mi:ss'));

insert?into?test?values?('張三',to_date('2013-02-04?15:23:22','yyyy-mm-dd?hh24:mi:ss'),to_date('2013-02-06?11:23:22','yyyy-mm-dd?hh24:mi:ss'),to_date('2013-02-06?14:23:22','yyyy-mm-dd?hh24:mi:ss'));

運行

select?姓名,

to_char(訪問時間,'yyyy-mm')?訪問月份,

to_char(trunc(round(sum(離開時間-進入時間)*1440)/60))||'小時'||to_char(round(sum((離開時間-進入時間)*1440))-trunc(round(sum(離開時間-進入時間)*1440)/60)*60)||'分鐘'?時間匯總,

count(*)?訪問次數?from?test?

group?by?姓名,to_char(訪問時間,'yyyy-mm');

結果

oracle 統計每年每月數據

第一步：創建表

--支出表??

create?table?PAY??

(??

ID???????NUMBER(11)?not?null,??

PAY_NAME?VARCHAR2(100),??

AUTHOR???VARCHAR2(100),??

TYPE_ID??NUMBER(11),??

PRICE????FLOAT,?--金額??

BRIEF????VARCHAR2(1000),??

USERID???NUMBER(11),?--用戶ID??

TIMES????DATE,?--時間??

PRIMARY?KEY(ID)??

)

第二步：插入數據

insert?into?PAY?(ID,?PAY_NAME,?AUTHOR,?TYPE_ID,?PRICE,?BRIEF,?USERID,?TIMES)??

values?(1,?'1',?'1',?0,?1,?'1',?1,?to_date('12-10-2012',?'dd-mm-yyyy'));??

insert?into?PAY?(ID,?PAY_NAME,?AUTHOR,?TYPE_ID,?PRICE,?BRIEF,?USERID,?TIMES)??

values?(0,?'3',?'3',?1,?3,?'3',?1,?to_date('12-10-2012',?'dd-mm-yyyy'));??

insert?into?PAY?(ID,?PAY_NAME,?AUTHOR,?TYPE_ID,?PRICE,?BRIEF,?USERID,?TIMES)??

values?(2,?'2',?'2',?1,?2,?'2343444',?1,?to_date('12-10-2012',?'dd-mm-yyyy'));??

insert?into?PAY?(ID,?PAY_NAME,?AUTHOR,?TYPE_ID,?PRICE,?BRIEF,?USERID,?TIMES)??

values?(6,?'6',?'1',?0,?4000,?'1',?1,?to_date('12-08-2012',?'dd-mm-yyyy'));??

insert?into?PAY?(ID,?PAY_NAME,?AUTHOR,?TYPE_ID,?PRICE,?BRIEF,?USERID,?TIMES)??

values?(5,?'5',?'1',?0,?1,?'1',?1,?to_date('12-10-2012',?'dd-mm-yyyy'));??

insert?into?PAY?(ID,?PAY_NAME,?AUTHOR,?TYPE_ID,?PRICE,?BRIEF,?USERID,?TIMES)??

values?(4,?'4',?'1',?0,?100,?'1',?1,?to_date('12-09-2012',?'dd-mm-yyyy'));??

insert?into?PAY?(ID,?PAY_NAME,?AUTHOR,?TYPE_ID,?PRICE,?BRIEF,?USERID,?TIMES)??

values?(7,?'4',?'1',?0,?566,?'1',?1,?to_date('03-03-2012',?'dd-mm-yyyy'));??

insert?into?PAY?(ID,?PAY_NAME,?AUTHOR,?TYPE_ID,?PRICE,?BRIEF,?USERID,?TIMES)??

values?(11,?'4',?'1',?0,?566,?'1',?1,?to_date('03-04-2012',?'dd-mm-yyyy'));??

insert?into?PAY?(ID,?PAY_NAME,?AUTHOR,?TYPE_ID,?PRICE,?BRIEF,?USERID,?TIMES)??

values?(10,?'4',?'1',?0,?566,?'1',?1,?to_date('03-05-2012',?'dd-mm-yyyy'));??

insert?into?PAY?(ID,?PAY_NAME,?AUTHOR,?TYPE_ID,?PRICE,?BRIEF,?USERID,?TIMES)??

values?(9,?'4',?'1',?0,?566,?'1',?1,?to_date('03-06-2012',?'dd-mm-yyyy'));??

insert?into?PAY?(ID,?PAY_NAME,?AUTHOR,?TYPE_ID,?PRICE,?BRIEF,?USERID,?TIMES)??

values?(8,?'4',?'1',?0,?566,?'1',?1,?to_date('03-07-2012',?'dd-mm-yyyy'));??

commit;

第三步：執行分組查詢

SELECT?SUM(DECODE(EXTRACT(MONTH?FROM?B.TIMES),?1,?B.PRICE,?0))?AS?A,??

SUM(DECODE(EXTRACT(MONTH?FROM?B.TIMES),?2,?B.PRICE,?0))?AS?B,??

SUM(DECODE(EXTRACT(MONTH?FROM?B.TIMES),?3,?B.PRICE,?0))?AS?C,??

SUM(DECODE(EXTRACT(MONTH?FROM?B.TIMES),?4,?B.PRICE,?0))?AS?D,??

SUM(DECODE(EXTRACT(MONTH?FROM?B.TIMES),?5,?B.PRICE,?0))?AS?E,??

SUM(DECODE(EXTRACT(MONTH?FROM?B.TIMES),?6,?B.PRICE,?0))?AS?F,??

SUM(DECODE(EXTRACT(MONTH?FROM?B.TIMES),?7,?B.PRICE,?0))?AS?G,??

SUM(DECODE(EXTRACT(MONTH?FROM?B.TIMES),?8,?B.PRICE,?0))?AS?H,??

SUM(DECODE(EXTRACT(MONTH?FROM?B.TIMES),?9,?B.PRICE,?0))?AS?I,??

SUM(DECODE(EXTRACT(MONTH?FROM?B.TIMES),?10,?B.PRICE,?0))?AS?J,??

SUM(DECODE(EXTRACT(MONTH?FROM?B.TIMES),?11,?B.PRICE,?0))?AS?K,??

SUM(DECODE(EXTRACT(MONTH?FROM?B.TIMES),?12,?B.PRICE,?0))?AS?L??

FROM?PAY?B??

WHERE?USERID?=?1??

AND?EXTRACT(YEAR?FROM?B.TIMES)?=?'2012'

第四步：輸出結果

oracle數據庫中怎么查詢當前年每月的天數，按自然月統計，謝謝！

樓上的只統計了一個月的吧

WITH?t?AS

(SELECT?????ROWNUM

FROM?DUAL

CONNECT?BY?ROWNUM?=?12)

SELECT????TO_CHAR?(LAST_DAY?(TO_DATE?(???TO_CHAR?(SYSDATE,?'YYYY')

||?LPAD?(TO_CHAR?(ROWNUM),?2,?0),

'YYYYMM'

)

),

'YYYY-MM'

)

||?'月',

TO_CHAR?(LAST_DAY?(TO_DATE?(???TO_CHAR?(SYSDATE,?'YYYY')

||?LPAD?(TO_CHAR?(ROWNUM),?2,?0),

'YYYYMM'

)

),

'DD'

)

||?'天'

FROM?t

直接全選復制粘貼運行

文章名稱：oracle如何按月統計 oracle按月分組統計
本文地址：http://m.jcarcd.cn/article/hjoooi.html