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

最新推荐文章于 2025-05-14 10:59:28 发布

mantisXF

最新推荐文章于 2025-05-14 10:59:28 发布

阅读量3.8k

点赞数

CC 4.0 BY-SA版权

文章标签： oracle numbers sql schema sqlserver table

本文链接：https://blog.youkuaiyun.com/mantisXF/article/details/2894602

本文介绍了一种使用SQL和自定义函数实现多条记录的分类汇总及连续数值合并的方法。通过具体实例展示了如何将同一组内的连续数值合并为一个范围，如“1001-1003”，并提供了适用于Oracle数据库的解决方案。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

今天有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;

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~~