ORACLE 连续数 并以逗号分隔 显示的问题

今天有guys在论坛里提出了连续数并分段显示成一行的问题，趁有时间留个记录以便日后查找，嘿嘿

 

Question:

 

如何对多条字符串记录进行"汇总"运算?
我有多条记录,要对其分类汇总.如下表,想通过形如 SELECT cardtype,sum(numbers) AS 数量, str_sum(CARDID) AS 号码范围 FROM card GROUP BY  cardtype.
原表记录:
cardtype numbers cardid
A 3 01001
A 2 01002
A 1 01003
A 2 01007
B 3 05005
B 5 05006
统计后形成:
cardtype 数量 号码范围
A 8 01001-01003,01007
B 8 05005-05006

现在这个str_sum()函数,就是我想象出来的功能,不能哪位大侠可以帮我写出解答?
当然,如果能够求出 01001,01002,01003,01007这样的结果也算是帮了大忙了,我再想办法变成01001-01003,01007的形式.
(注:我是想用vs2008c#+sqlserver2005写一个简单的项目,如果能用oracle写的SQL也行)

 

本人用SQL和FUNCTION两种方式实现：

 

1> SQL:

SQL> SELECT * FROM SCOTT.CARD;

CARDTYPE      NUMBERS CARDID
---------- ---------- ----------
A                   3 01001
A                   2 01002
A                   1 01003
A                   2 01007
B                   3 05005
B                   5 05006

6 rows selected

如果不支持WMSYS.WM_CONCAT函数(10g里才有)，用下面的方法:
SQL> SELECT DISTINCT CARDTYPE,
  2                  COUNTS,
  3                  LTRIM(FIRST_VALUE(SYS_CONNECT_BY_PATH(SEGMENTS, ','))
  4                        OVER(PARTITION BY CARDTYPE ORDER BY LEVEL DESC),
  5                        ',') "STR_SUM"
  6    FROM (SELECT CARDTYPE,
  7                 COUNTS,
  8                 SEGMENTS,
  9                 CARDTYPE || ROW_NUMBER() OVER(PARTITION BY CARDTYPE ORDER BY SEGMENTS) "FRONTS",
 10                 CARDTYPE ||
 11                 (ROW_NUMBER()
 12                  OVER(PARTITION BY CARDTYPE ORDER BY SEGMENTS) + 1) "BEHINDS"
 13            FROM (SELECT CARDTYPE,
 14                         COUNTS,
 15                         DECODE(MIN(CARDID),
 16                                MAX(CARDID),
 17                                MIN(CARDID),
 18                                MIN(CARDID) || '-' || MAX(CARDID)) "SEGMENTS"
 19                    FROM (SELECT CARDTYPE,
 20                                 CARDID,
 21                                 SUM(NUMBERS) OVER(PARTITION BY CARDTYPE) "COUNTS",
 22                                 TO_NUMBER(CARDID) -
 23                                 (ROW_NUMBER()
 24                                  OVER(PARTITION BY CARDTYPE ORDER BY CARDID)) DIF
 25                            FROM SCOTT.CARD)
 26                   GROUP BY CARDTYPE, COUNTS, DIF) T2) T3
 27  CONNECT BY PRIOR BEHINDS = FRONTS;

CARDTYPE       COUNTS STR_SUM
---------- ---------- --------------------------------------------------------------------------------
A                   8 01001-01003,01007
B                   8 05005-05006

如果支持WMSYS.WM_CONCAT函数，用下面的方法:
SQL> SELECT CARDTYPE, COUNTS, WMSYS.WM_CONCAT(SEGMENTS) "STR_SUM"
  2    FROM (SELECT CARDTYPE,
  3                 COUNTS,
  4                 DECODE(MIN(CARDID),
  5                        MAX(CARDID),
  6                        MIN(CARDID),
  7                        MIN(CARDID) || '-' || MAX(CARDID)) "SEGMENTS"
  8            FROM (SELECT CARDTYPE,
  9                         CARDID,
 10                         SUM(NUMBERS) OVER(PARTITION BY CARDTYPE) "COUNTS",
 11                         TO_NUMBER(CARDID) -
 12                         (ROW_NUMBER()
 13                          OVER(PARTITION BY CARDTYPE ORDER BY CARDID)) DIF
 14                    FROM SCOTT.CARD)
 15           GROUP BY CARDTYPE, COUNTS, DIF) T1
 16   GROUP BY CARDTYPE, COUNTS;

CARDTYPE       COUNTS STR_SUM
---------- ---------- --------------------------------------------------------------------------------
A                   8 01001-01003,01007
B                   8 05005-05006

SQL>

 

2> FUNCTION:

 

/*
     Author: mantisXF
     Date: Sep 7, 2008
    
     GROUPID      -- Your group data vlaue   E.G: 'A' OR COLUMN_NAME: CARDTYPE
     GROUPNAME    -- Your group column name
     COLUMN_NAME  -- Your column name which you need to return
     SCHEMA_NAME  -- Your schema name of table
     TABLE_NAME   -- Your table name

*/

CREATE OR REPLACE FUNCTION SCOTT.GET_CONTINUUM_NUMBERS(GROUPID IN VARCHAR2,
                                                 GROUPNAME IN VARCHAR2,
                                                 COLUMN_NAME IN VARCHAR2,
                                                 SCHEMA_NAME IN VARCHAR2,
                                                 TABLE_NAME IN VARCHAR2) RETURN VARCHAR2 IS
  -- DECLARE VARIABLE
  CUR_NUMBERS SYS_REFCURSOR;
  V_CON_NUMBERS VARCHAR2(1000) := '';
  V_MIN_NUMBERS VARCHAR2(30) := '';
  V_MAX_NUMBERS VARCHAR2(30) := '';
  V_TEMP_NUMBERS VARCHAR2(30) := '';
  V_SQL VARCHAR2(100);

  V_STEP VARCHAR2(100) := '';
 
BEGIN
  V_STEP := 'GET THE CURSOR BY PARTICULARLY GROUP_ID';
  V_SQL := 'SELECT '||COLUMN_NAME||' FROM '||SCHEMA_NAME||'.'||TABLE_NAME||' WHERE '||GROUPNAME||' = :X ORDER BY '||COLUMN_NAME||' ';
  DBMS_OUTPUT.PUT_LINE('V_SQL: '||V_SQL);
  OPEN CUR_NUMBERS FOR V_SQL USING GROUPID;
 
  V_STEP := 'LOOP THE RESULT';
  LOOP
  FETCH CUR_NUMBERS INTO V_TEMP_NUMBERS;
 
    V_STEP := 'DECIDE WHETHER V_TEMP_NUMBERS HAVING VALUE';
    IF LENGTH(V_TEMP_NUMBERS) > 0 THEN
      V_STEP := 'DECIDE WHETHER V_MIN_NUMBE HAVING VALUE';
      IF V_MAX_NUMBERS IS NOT NULL THEN
        V_STEP := 'DECIDE WHETHER IT HAVING CONTINUUM_NUMBERS VALUE';
        IF TO_NUMBER(V_TEMP_NUMBERS) - NVL(TO_NUMBER(V_MAX_NUMBERS),0) = 1 THEN
          V_MAX_NUMBERS := V_TEMP_NUMBERS;
        ELSE
          V_STEP := 'DECIDE WHETHER V_MIN_NUMBE = V_MAX_NUMB. IT MEANS JUST HAVING 1 VALUE';
          IF V_MIN_NUMBERS = V_MAX_NUMBERS THEN
            V_CON_NUMBERS := V_CON_NUMBERS||','||V_MIN_NUMBERS;
          ELSE
            V_CON_NUMBERS := V_CON_NUMBERS||','||V_MIN_NUMBERS||'-'||V_MAX_NUMBERS;
            V_MIN_NUMBERS := V_TEMP_NUMBERS;
            V_MAX_NUMBERS := V_TEMP_NUMBERS;
          END IF;
        END IF;
      ELSE
        V_MIN_NUMBERS := V_TEMP_NUMBERS;
        V_MAX_NUMBERS := V_TEMP_NUMBERS;
      END IF;
    ELSE
      V_CON_NUMBERS := V_CON_NUMBERS||V_TEMP_NUMBERS;
    END IF;
 
    EXIT WHEN CUR_NUMBERS%NOTFOUND;

  END LOOP;
 
  V_STEP := 'CLOSE CURSOR CUR_NUMBERS';
  IF CUR_NUMBERS%ISOPEN THEN
    CLOSE CUR_NUMBERS;
  END IF;
 
  V_STEP := 'RETURN V_CON_NUMBERS';
  RETURN(LTRIM(V_CON_NUMBERS,','));

EXCEPTION
   WHEN OTHERS THEN
   DBMS_OUTPUT.PUT_LINE('Error at: '||V_STEP);
   DBMS_OUTPUT.PUT_LINE(SQLCODE||' : '||SQLERRM);
 
END GET_CONTINUUM_NUMBERS;

-- 实现方式：

SQL> SELECT CARDTYPE,
  2         SUM(NUMBERS),
  3         SCOTT.GET_CONTINUUM_NUMBERS(CARDTYPE,'CARDTYPE','CARDID','SCOTT','CARD') "STR_SUM"
  4    FROM SCOTT.CARD
  5   GROUP BY CARDTYPE;

CARDTYPE   SUM(NUMBERS) STR_SUM
---------- ------------ --------------------------------------------------------------------------------
A                     8 01001-01003,01007
B                     8 05005-05006

SQL>

 

函数的方法试着去适合所有表和相关列情况，如果有什么不对的地方欢迎指出。

 

Good luck~~