上旬:1-10 中旬:11-20 下旬:21-31,部分月会多一天select trunc(日期,'month'), decode(trunc((日期-trunc(日期,'month'))/10),0,'上旬',1,'中旬',2,'下旬',3,'下旬',null), sum(金额) from ttt group by trunc(日期,'month'), decode(trunc((日期-trunc(日期,'month'))/10),0,'上旬',1,'中旬',2,'下旬',3,'下旬',null)
SELECT MTH, XUN, SUM(QTY) FROM (SELECT TO_CHAR(DAY_ID, 'YYYYMM') AS MTH, CASE WHEN TO_NUMBER(TO_CHAR(DAY_ID, 'DD')) < 11 THEN 1 WHEN TO_NUMBER(TO_CHAR(DAY_ID, 'DD')) < 21 THEN 2 ELSE 3 END AS XUN, QTY FROM YOURTABLE) GROUP BY MTH, XUN ORDER BY MTH, XUN
CREATE TABLE TAB1(DATETIME DATE,MONEY INTEGER); INSERT INTO TAB1 VALUES(TO_DATE('20071201','YYYYMMDD'),1000); INSERT INTO TAB1 VALUES(TO_DATE('20071211','YYYYMMDD'),2000); INSERT INTO TAB1 VALUES(TO_DATE('20071221','YYYYMMDD'),3000); INSERT INTO TAB1 VALUES(TO_DATE('20071202','YYYYMMDD'),4000); INSERT INTO TAB1 VALUES(TO_DATE('20071203','YYYYMMDD'),5000); INSERT INTO TAB1 VALUES(TO_DATE('20071225','YYYYMMDD'),6000); INSERT INTO TAB1 VALUES(TO_DATE('20070201','YYYYMMDD'),7000); INSERT INTO TAB1 VALUES(TO_DATE('20070221','YYYYMMDD'),8000); INSERT INTO TAB1 VALUES(TO_DATE('20070211','YYYYMMDD'),9000); INSERT INTO TAB1 VALUES(TO_DATE('20070201','YYYYMMDD'),9000); INSERT INTO TAB1 VALUES(TO_DATE('20070211','YYYYMMDD'),9000); INSERT INTO TAB1 VALUES(TO_DATE('20070221','YYYYMMDD'),9000); INSERT INTO TAB1 VALUES(TO_DATE('20070131','YYYYMMDD'),9000); COMMIT;SELECT DATETIME,SUM(MONEY) MONEY FROM (SELECT TO_CHAR(DATETIME,'YYYY') || '年' || TO_CHAR(DATETIME,'MM') || '月' || DECODE(TRUNC((TO_CHAR(DATETIME,'DD'))/10),0,'上旬',1,'中旬','下旬') DATETIME, money from tab1) GROUP BY DATETIME;DATETIME MONEY -------------- ---------- 2007年01月下旬 9000 2007年02月上旬 16000 2007年02月下旬 17000 2007年02月中旬 18000 2007年12月上旬 10000 2007年12月下旬 9000 2007年12月中旬 2000已选择7行。
中旬:11-20
下旬:21-31,部分月会多一天select trunc(日期,'month'),
decode(trunc((日期-trunc(日期,'month'))/10),0,'上旬',1,'中旬',2,'下旬',3,'下旬',null),
sum(金额)
from ttt
group by
trunc(日期,'month'),
decode(trunc((日期-trunc(日期,'month'))/10),0,'上旬',1,'中旬',2,'下旬',3,'下旬',null)
FROM (SELECT TO_CHAR(DAY_ID, 'YYYYMM') AS MTH,
CASE
WHEN TO_NUMBER(TO_CHAR(DAY_ID, 'DD')) < 11 THEN
1
WHEN TO_NUMBER(TO_CHAR(DAY_ID, 'DD')) < 21 THEN
2
ELSE
3
END AS XUN,
QTY
FROM YOURTABLE)
GROUP BY MTH, XUN
ORDER BY MTH, XUN
CREATE TABLE TAB1(DATETIME DATE,MONEY INTEGER);
INSERT INTO TAB1 VALUES(TO_DATE('20071201','YYYYMMDD'),1000);
INSERT INTO TAB1 VALUES(TO_DATE('20071211','YYYYMMDD'),2000);
INSERT INTO TAB1 VALUES(TO_DATE('20071221','YYYYMMDD'),3000);
INSERT INTO TAB1 VALUES(TO_DATE('20071202','YYYYMMDD'),4000);
INSERT INTO TAB1 VALUES(TO_DATE('20071203','YYYYMMDD'),5000);
INSERT INTO TAB1 VALUES(TO_DATE('20071225','YYYYMMDD'),6000);
INSERT INTO TAB1 VALUES(TO_DATE('20070201','YYYYMMDD'),7000);
INSERT INTO TAB1 VALUES(TO_DATE('20070221','YYYYMMDD'),8000);
INSERT INTO TAB1 VALUES(TO_DATE('20070211','YYYYMMDD'),9000);
INSERT INTO TAB1 VALUES(TO_DATE('20070201','YYYYMMDD'),9000);
INSERT INTO TAB1 VALUES(TO_DATE('20070211','YYYYMMDD'),9000);
INSERT INTO TAB1 VALUES(TO_DATE('20070221','YYYYMMDD'),9000);
INSERT INTO TAB1 VALUES(TO_DATE('20070131','YYYYMMDD'),9000);
COMMIT;SELECT DATETIME,SUM(MONEY) MONEY FROM
(SELECT TO_CHAR(DATETIME,'YYYY') || '年' || TO_CHAR(DATETIME,'MM') || '月' ||
DECODE(TRUNC((TO_CHAR(DATETIME,'DD'))/10),0,'上旬',1,'中旬','下旬') DATETIME,
money from tab1)
GROUP BY DATETIME;DATETIME MONEY
-------------- ----------
2007年01月下旬 9000
2007年02月上旬 16000
2007年02月下旬 17000
2007年02月中旬 18000
2007年12月上旬 10000
2007年12月下旬 9000
2007年12月中旬 2000已选择7行。