超难SQL求助，对连续时间区域分组求和

Question

模拟数据如下：
20090901    10
20090902    20
20090903    7
20090904    5
20090906    5
20090908    5要求每相邻的两天作为一个单位统计，不能连续单天统计
结果如下
20090901+20090902   30
20090903+20090904   12
20090906+20090907   5
20090908+20090909   5
请大家不吝赐教，拿出可行方案者另有分谢

Answer 1

先取出最小时间，20090901然后用时间减去最小时间20090901的时间差去除2，以此分组比如，
20090901，20090902两条记录，减掉最小时间后除以2，floor处理下，结果是0；
20090903，20090904两条记录，减掉最小时间后除以2，floor处理下，结果是1；所以有：
select replace(wmsys.wm_concat(date),',','+'),sum(numval) from tablename 
group by floor((date - to_date('20090901','yyyymmdd'))/2);

Answer 2

---SQL
select b.a||'+'||b.b a, sum(a.c2) c2 from t a,
(select a,b from
(SELECT TO_CHAR(SYSDATE,'YYYYMM')||LPAD(ROWNUM,2,'0') A,
TO_CHAR(SYSDATE,'YYYYMM')||LPAD(ROWNUM+1,2,'0') B,rownum rn FROM DUAL
CONNECT BY LEVEL<=30) where mod(rn,2)=1) b
where a.c1=b.a or a.c1=b.b
group by b.a,b.b---结果
20090901+20090902 30
20090903+20090904 12
20090905+20090906 5
20090907+20090908 5

Answer 3

select replace(wm_concat(col1),',','+')newcol1,
  sum(col2)newcol2
from tt
group by trunc((rownum-1)/2)  

Answer 4

没看清楚，需要的应该是这个结果
select * from test_pA B
20090901 10
20090902 20
20090903 7
20090904 5
20090906 5
20090908 5select replace(wm_concat(a),',','+')newcol1, 
  sum(nvl(b,0))newcol2 
  from
 ( select a.a,b.b 
from (select to_char(((select min(to_date(a,'YYYYMMDD')) from test_p)+rownum-1),'YYYYMMDD')a
from dual 
connect by rownum<(select max(to_date(a,'YYYYMMDD'))-min(to_date(a,'YYYYMMDD'))+2 from test_p)) a 
left join test_p b
on a.a=b.a
order by a.a)
group by trunc((rownum-1)/2)  NEWCOL1 NEWCOL2
20090901+20090902 30
20090903+20090904 12
20090905+20090906 5
20090907+20090908 5

Answer 5

select replace(wmsys.wm_concat(date),',','+'),
       sum(numval)
from  (select t.*, row_number() over(order by date) rn from tablename t) 
group by floor(rn/2);

Answer 6

有点小错误，修改下：
select replace(wmsys.wm_concat(date),',','+'),
       sum(numval)
from  (select t.*, row_number() over(order by date) rn from tablename t) 
group by floor((rn+1)/2);

Answer 7

SQL> select n, to_char(d, 'yyyymmdd') d from test_a order by d asc;         N D
---------- --------
        12 20090901
        14 20090902
        14 20090903
        11 20090906
        12 20090907
        13 20090908
        14 20090909SQL>select trunc((rownum-1)/2) no, sum(n),  replace(wmsys.wm_concat(to_char(d,'yyyymmdd')),',','+') title from (select d, n from test_a order by d asc) t group by trunc((rownum-1)/2) order by trunc((rownum-1)/2)        NO     SUM(N) TITLE
---------- ---------- ------------------------------
         0         26 20090901+20090902
         1         25 20090903+20090906
         2         25 20090907+20090908
         3         29 20090909+20090910
         4         33 20090911+20090912
         5         37 20090913+20090914  

Answer 8

整理一下
select trunc((rownum-1)/2) no, sum(n),  
      replace(wmsys.wm_concat(to_char(d,'yyyymmdd')),',','+') title 
      from 
      (select d, n from test_a order by d asc) t 
      group by trunc((rownum-1)/2) 
      order by trunc((rownum-1)/2)

Answer 9

select case when length(a)=8 then a||'+'||to_char(to_date(a,'yyyymmdd')+1,'yyyymmdd')
  else replace(a,',','+') end a,b from(
select wm_concat(a)a,sum(b)b from(
select a,b,
  row_number()over(partition by to_date(a,'YYYYMMDD')-rownum order by a)rn,
  dense_rank()over(order by to_date(a,'YYYYMMDD')-rownum)dk
from (
select * from test_p order by a)  )
group by dk,trunc((rn-1)/2))A B
20090901+20090902 30
20090903+20090904 12
20090906+20090907 5
20090908+20090909 5
楼主看看这样对不

Answer 10

select case when length(a)=8 then a||'+'||to_char(to_date(a,'yyyymmdd')+1,'yyyymmdd')
  else replace(a,',','+') end a,b from(
   select wm_concat(a)a,sum(b)b from(
      select a,b,
        row_number()over(partition by to_date(a,'YYYYMMDD')-rownum order by a)rn,
        dense_rank()over(order by to_date(a,'YYYYMMDD')-rownum)dk
      from (
         select * from test_p order by a)  )
   group by dk,trunc((rn-1)/2))
select * from test_pA B
20090901 10
20090902 20
20090903 7
20090904 5
20090906 5
20090908 5A B
20090901+20090902 30
20090903+20090904 12
20090906+20090907 5
20090908+20090909 5

Answer 11

#14改下最外层select去掉||'+'||to_char(to_date(a,'yyyymmdd')+1,'yyyymmdd')
就符合了

Answer 12

wildwave 兄，照上面那样如果改成统计3天能改吗？
row_number()over(partition by to_date(a,'YYYYMMDD')-rownum order by a)rn,
你的这个用法我以前没用过，学习了

Answer 13

trunc((rn-1)/2)->
floor((rn-1)/3)

Answer 14

3个的话用一条sql想不出来符合要求的分组办法..
能想到只有：先把日期全部列出来，与原表连接
然后将不符合条件的记录删掉，比如20090907这条记录
然后再3个一组进行分组

Answer 15

3个的话用一条sql想不出来符合要求的分组办法..
能想到只有：先把日期全部列出来，与原表连接
然后将不符合条件的记录删掉，比如20090907这条记录
然后再3个一组进行分组

Answer 16

为什么一定要一句sql解决呢？
程序不是万能的，sql也不是万能的，最好两者结合一下

Answer 17

scott@ORCL10G> CREATE TABLE test_tb(
  2  NO NUMBER(3,0), 
  3  Datetime1 DATE, 
  4  Datetime2 DATE, 
  5  Dategroup VARCHAR2(30),
  6  Num NUMBER(7,0));表已创建。scott@ORCL10G> INSERT INTO test_tb(Datetime1, Num)
  2  VALUES(TO_DATE('20090901','yyyymmdd'), 10);已创建 1 行。scott@ORCL10G> INSERT INTO test_tb(Datetime1, Num)
  2  VALUES(TO_DATE('20090902','yyyymmdd'), 20);已创建 1 行。scott@ORCL10G> INSERT INTO test_tb(Datetime1, Num)
  2  VALUES(TO_DATE('20090903','yyyymmdd'), 7);已创建 1 行。scott@ORCL10G> INSERT INTO test_tb(Datetime1, Num)
  2  VALUES(TO_DATE('20090904','yyyymmdd'), 5);已创建 1 行。scott@ORCL10G> INSERT INTO test_tb(Datetime1, Num)
  2  VALUES(TO_DATE('20090906','yyyymmdd'), 5);已创建 1 行。scott@ORCL10G> INSERT INTO test_tb(Datetime1, Num)
  2  VALUES(TO_DATE('20090908','yyyymmdd'), 5);已创建 1 行。scott@ORCL10G> 
scott@ORCL10G> SELECT * FROM test_tb;        NO DATETIME1      DATETIME2      DATEGROUP                             NUM
---------- -------------- -------------- ------------------------------ ----------
           01-9月 -09                                                           10
           02-9月 -09                                                           20
           03-9月 -09                                                            7
           04-9月 -09                                                            5
           06-9月 -09                                                            5
           08-9月 -09                                                            5已选择6行。scott@ORCL10G> 
scott@ORCL10G> UPDATE test_tb SET Datetime2=Datetime1;已更新6行。scott@ORCL10G> 
scott@ORCL10G> SELECT * FROM test_tb;        NO DATETIME1      DATETIME2      DATEGROUP                             NUM
---------- -------------- -------------- ------------------------------ ----------
           01-9月 -09     01-9月 -09                                            10
           02-9月 -09     02-9月 -09                                            20
           03-9月 -09     03-9月 -09                                             7
           04-9月 -09     04-9月 -09                                             5
           06-9月 -09     06-9月 -09                                             5
           08-9月 -09     08-9月 -09                                             5已选择6行。scott@ORCL10G> 
scott@ORCL10G> UPDATE test_tb SET Dategroup=
  2  TO_CHAR(TO_DATE(TO_CHAR(CASE WHEN MOD(TO_CHAR(Datetime1,'dd'),2)<>'0' THEN Datetime2+1 ELSE Dat
etime2 END,'yyyymmdd'),'yyyymmdd')-1,'yyyymmdd')
  3  ||'+'||TO_CHAR(CASE WHEN MOD(TO_CHAR(Datetime1,'dd'),2)<>'0' THEN Datetime2+1 ELSE Datetime2 EN
D,'yyyymmdd');已更新6行。scott@ORCL10G> 
scott@ORCL10G> SELECT * FROM test_tb;        NO DATETIME1      DATETIME2      DATEGROUP                             NUM
---------- -------------- -------------- ------------------------------ ----------
           01-9月 -09     01-9月 -09     20090901+20090902                      10
           02-9月 -09     02-9月 -09     20090901+20090902                      20
           03-9月 -09     03-9月 -09     20090903+20090904                       7
           04-9月 -09     04-9月 -09     20090903+20090904                       5
           06-9月 -09     06-9月 -09     20090905+20090906                       5
           08-9月 -09     08-9月 -09     20090907+20090908                       5已选择6行。scott@ORCL10G> 
scott@ORCL10G> SELECT Dategroup, SUM(Num) AS SUM_Num
  2   FROM  test_tb
  3  GROUP BY Dategroup
  4  ORDER BY SUM(Num) DESC;DATEGROUP                         SUM_NUM
------------------------------ ----------
20090901+20090902                      30
20090903+20090904                      12
20090905+20090906                       5
20090907+20090908                       5已选择4行。scott@ORCL10G> 

Answer 18

scott@ORCL10G> CREATE TABLE test_tb(
  2  Datetime1 DATE, 
  3  Datetime2 DATE, 
  4  Dategroup VARCHAR2(30),
  5  Num NUMBER(7,0));表已创建。scott@ORCL10G> 
scott@ORCL10G> INSERT INTO test_tb(Datetime1, Num)
  2  VALUES(TO_DATE('20090901','yyyymmdd'), 10);已创建 1 行。scott@ORCL10G> INSERT INTO test_tb(Datetime1, Num)
  2  VALUES(TO_DATE('20090902','yyyymmdd'), 20);已创建 1 行。scott@ORCL10G> INSERT INTO test_tb(Datetime1, Num)
  2  VALUES(TO_DATE('20090903','yyyymmdd'), 7);已创建 1 行。scott@ORCL10G> INSERT INTO test_tb(Datetime1, Num)
  2  VALUES(TO_DATE('20090904','yyyymmdd'), 5);已创建 1 行。scott@ORCL10G> INSERT INTO test_tb(Datetime1, Num)
  2  VALUES(TO_DATE('20090906','yyyymmdd'), 5);已创建 1 行。scott@ORCL10G> INSERT INTO test_tb(Datetime1, Num)
  2  VALUES(TO_DATE('20090908','yyyymmdd'), 5);已创建 1 行。scott@ORCL10G> 
scott@ORCL10G> SELECT * FROM test_tb;DATETIME1      DATETIME2      DATEGROUP                             NUM
-------------- -------------- ------------------------------ ----------
01-9月 -09                                                           10
02-9月 -09                                                           20
03-9月 -09                                                            7
04-9月 -09                                                            5
06-9月 -09                                                            5
08-9月 -09                                                            5已选择6行。scott@ORCL10G> 
scott@ORCL10G> UPDATE test_tb SET Datetime2=Datetime1;已更新6行。scott@ORCL10G> 
scott@ORCL10G> SELECT * FROM test_tb;DATETIME1      DATETIME2      DATEGROUP                             NUM
-------------- -------------- ------------------------------ ----------
01-9月 -09     01-9月 -09                                            10
02-9月 -09     02-9月 -09                                            20
03-9月 -09     03-9月 -09                                             7
04-9月 -09     04-9月 -09                                             5
06-9月 -09     06-9月 -09                                             5
08-9月 -09     08-9月 -09                                             5已选择6行。scott@ORCL10G> 
scott@ORCL10G> UPDATE test_tb SET Dategroup=
  2  TO_CHAR(TO_DATE(TO_CHAR(CASE WHEN MOD(TO_CHAR(Datetime1,'dd'),2)<>'0' THEN Datetime2+1 ELSE Dat
etime2 END,'yyyymmdd'),'yyyymmdd')-1,'yyyymmdd')
  3  ||'+'||TO_CHAR(CASE WHEN MOD(TO_CHAR(Datetime1,'dd'),2)<>'0' THEN Datetime2+1 ELSE Datetime2 EN
D,'yyyymmdd');已更新6行。scott@ORCL10G> 
scott@ORCL10G> UPDATE test_tb SET Dategroup=TO_CHAR(Datetime1,'yyyymmdd')
  2  WHERE Dategroup IN (SELECT Dategroup FROM test_tb GROUP BY Dategroup HAVING COUNT(Dategroup)=1)
;已更新2行。scott@ORCL10G> 
scott@ORCL10G> SELECT * FROM test_tb;DATETIME1      DATETIME2      DATEGROUP                             NUM
-------------- -------------- ------------------------------ ----------
01-9月 -09     01-9月 -09     20090901+20090902                      10
02-9月 -09     02-9月 -09     20090901+20090902                      20
03-9月 -09     03-9月 -09     20090903+20090904                       7
04-9月 -09     04-9月 -09     20090903+20090904                       5
06-9月 -09     06-9月 -09     20090906                                5
08-9月 -09     08-9月 -09     20090908                                5已选择6行。scott@ORCL10G> 
scott@ORCL10G> SELECT Dategroup, SUM(Num) AS SUM_Num
  2   FROM  test_tb
  3  GROUP BY Dategroup
  4  ORDER BY SUM(Num) DESC;DATEGROUP                         SUM_NUM
------------------------------ ----------
20090901+20090902                      30
20090903+20090904                      12
20090906                                5
20090908                                5已选择4行。scott@ORCL10G> 

Answer 19

select trunc((rownum-1)/2) no, sum(n),  
      replace(wmsys.wm_concat(to_char(d,'yyyymmdd')),',','+') title 
      from 
      (select d, n from test_a order by d asc) t 
      group by trunc((rownum-1)/2) 
      order by trunc((rownum-1)/2)

Answer 20

scott@ORCL10G 是什么意思.请解释一下.

Answer 21

楼上的：用过SQL Plus没？你可以把
SQL>   变成 贾瑞民>    但不会影响你在里面执行任何语句