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

本文介绍了一种使用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;

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

 

### Oracle 数据库中将列数据转换为以逗号分隔的行数据的方法 在 Oracle 数据库中,可以通过 SQL 查询实现将存储在一个字段内的逗号分隔字符串拆分为多行记录。这种方法适用于处理类似权限编号或其他由逗号分割的数据场景。 以下是具体的解决方案: #### 方法一:使用 `CONNECT BY` 和 `REGEXP_SUBSTR` 通过正则表达式函数 `REGEXP_SUBSTR` 结合层次化查询 (`CONNECT BY`) 来实现列转行为逗号分隔的形式。此方法利用了 Oracle 的递归特性来逐一分割字符串中的每一部分。 ```sql WITH sample_data AS ( SELECT '1,2,3,4,5' AS column_with_commas FROM DUAL ) SELECT TRIM(REGEXP_SUBSTR(column_with_commas, '[^,]+', 1, LEVEL)) AS split_value FROM sample_data CONNECT BY LEVEL <= REGEXP_COUNT(column_with_commas, ',') + 1; ``` 这段代码的作用如下: - 使用 `REGEXP_SUBSTR` 提取逗号之间的子串[^1]。 - 层次化查询 `CONNECT BY` 控制提取的层数等于逗号的数量加一[^4]。 - `TRIM` 去除可能存在的多余空白字符[^2]。 --- #### 方法二:基于 XMLType 转换 另一种方式是借助 Oracle 的 XML 功能,将逗号分隔的字符串转化为 XML 格式的节点集合,再将其解析为单独的行。 ```sql WITH sample_data AS ( SELECT '1,2,3,4,5' AS column_with_commas FROM DUAL ) SELECT COLUMN_VALUE AS split_value FROM TABLE(XMLSequence(EXTRACT(XMLTYPE('<r><i>' || REPLACE(column_with_commas, ',', '</i><i>') || '</i></r>'), '/r/i'))) CROSS JOIN (SELECT * FROM sample_data); ``` 这里的逻辑分解为: - 将原始字符串替换为 XML 形式的标签结构 `<i>`[^3]。 - 使用 `XMLSequence` 解析 XML 并返回每一段作为独立的结果集[^4]。 --- #### 方法三:PL/SQL 自定义函数 对于复杂的业务需求,也可以编写一个 PL/SQL 函数来进行动态拆分操作。这种方式虽然灵活性更高,但在简单场景下不如纯 SQL 高效。 ```plsql CREATE OR REPLACE FUNCTION SPLIT_STRING(p_str IN VARCHAR2, p_delimiter IN VARCHAR2) RETURN SYS.ODCIVARCHAR2LIST PIPELINED IS l_string LONG := p_str || p_delimiter; l_index PLS_INTEGER; BEGIN LOOP l_index := INSTR(l_string, p_delimiter); EXIT WHEN NVL(l_index, 0) = 0; PIPE ROW(SUBSTR(l_string, 1, l_index - 1)); l_string := SUBSTR(l_string, l_index + LENGTH(p_delimiter)); END LOOP; END; / -- 测试调用 SELECT COLUMN_VALUE AS split_value FROM TABLE(SPLIT_STRING('1,2,3,4,5', ',')); DROP FUNCTION SPLIT_STRING; ``` 以上脚本创建了一个名为 `SPLIT_STRING` 的管道函数,它接受两个参数——待拆分的字符串以及分隔符,并逐步切割输入字符串并返回结果。 --- ### 性能对比与适用范围 三种方法各有优劣: - **方法一** 是最常用的方案之一,适合大多数情况下的静态或半静态数据处理。 - **方法二** 更适合需要跨平台兼容性的场合,尤其是当目标环境支持标准 XML 处理时[^3]。 - **方法三** 对于频繁使用的复杂逻辑更为合适,但由于涉及过程化编程,维护成本较高。 无论采用哪种技术路线,在实际应用前都应充分测试其性能表现,尤其是在大数据量条件下验证执行计划是否合理。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值