如何对多条字符串记录进行"汇总"运算?
我有多条记录,要对其分类汇总.如下表,想通过形如 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也行)
我有多条记录,要对其分类汇总.如下表,想通过形如 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也行)
已写入 file afiedt.buf 1 create or replace function str_num(ctype in varchar2)
2 return varchar2
3 is
4 rs varchar2(4000);
5 begin
6 for x in (select cardid from t where cardtype=ctype) loop
7 rs:=rs||','||x.cardid;
8 end loop;
9 return rs;
10* end;
11 /函数已创建。SQL> select str_num(cardtype) from t group by cardtype;STR_NUM(CARDTYPE)
-------------------------------------------------------------------------,01001,01002,01003,01007
,05006,05005SQL> select * from t;CA NUMBERS CARDID
-- ---------- ----------
A 1 01001
A 2 01002
A 3 01003
A 2 01007
B 3 05006
B 5 05005已选择6行。前面多了个逗号，你自己再处理一下吧
-- ---------- ----------------------------------------
A 8 ,01001,01002,01003,01007
B 8 ,05006,05005
DROP TABLE t;
CREATE TABLE t(cardtype VARCHAR2(10),numbers INT,cardid VARCHAR2(10));
INSERT INTO T VALUES ('A', 3, '01001');
INSERT INTO T VALUES ('A', 2, '01002');
INSERT INTO T VALUES ('A', 1, '01003');
INSERT INTO T VALUES ('A', 2, '01007');
INSERT INTO T VALUES ('B', 3, '05005');
INSERT INTO T VALUES ('B', 5, '05006');CREATE OR REPLACE FUNCTION MY_CONCAT(P_cardtype VARCHAR2) RETURN VARCHAR2 IS
V_STR VARCHAR2(2000) := '';
TYPE XX IS REF CURSOR;
R XX;
V_TEMP_STR VARCHAR2(100);
BEGIN
OPEN R FOR 'SELECT cardid FROM t WHERE cardtype=''' || P_cardtype||'''';
LOOP
FETCH R
INTO V_TEMP_STR;
EXIT WHEN R%NOTFOUND;
IF LENGTH(V_STR) > 0 THEN
V_STR := V_STR || ',' || V_TEMP_STR;
ELSE
V_STR := V_TEMP_STR;
END IF;
END LOOP;
CLOSE R;
RETURN(V_STR);
END MY_CONCAT;
/
SELECT cardtype,MY_CONCAT(cardtype) CARDID FROM t GROUP BY cardtype;
--或者:
SELECT CARDTYPE,wmsys.wm_concat(CARDID)
FROM t GROUP BY cardtype;
/*
输出:
CARDTYPE CARDTYPE
A 01001,01002,01003,01007
B 05005,05006
*/SELECT CARDTYPE,wmsys.wm_concat(CARDID) CARDID
FROM (SELECT CARDTYPE,
DECODE(TO_CHAR(MAX(CARDID)),
TO_CHAR(MIN(CARDID)),
TO_CHAR(MIN(CARDID)),
TO_CHAR(MIN(CARDID)) || '-' || TO_CHAR(MAX(CARDID))) CARDID
FROM (SELECT CARDTYPE, CARDID, COL, COL - ROWNUM COL1
FROM (SELECT CARDTYPE, CARDID, N COL
FROM (SELECT CARDTYPE, CARDID, TO_NUMBER(CARDID) N
FROM T)
ORDER BY CARDTYPE, N))
GROUP BY CARDTYPE, COL1
ORDER BY 1, 2)
GROUP BY CARDTYPE;/*输出:
CARDTYPE CARDID
A 01001-01003,01007
B 05005-05006
*/
SQL> SELECT * FROM CARD;CARDTYPE NUMBERS CARID
---------- ---------- ----------
A 3 01001
A 2 01002
A 1 01003
A 2 01007
B 3 05005
B 5 050066 rows selectedSQL>
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 ',')
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 MAX(COUNTS) "COUNTS",
15 DECODE(MIN(CARID),
16 MAX(CARID),
17 MIN(CARID),
18 MIN(CARID) || '-' || MAX(CARID)) "SEGMENTS"
19 FROM (SELECT CARDTYPE,
20 CARID,
21 COUNT(1) OVER(PARTITION BY CARDTYPE) "COUNTS",
22 TO_NUMBER(CARID) -
23 (ROW_NUMBER()
24 OVER(PARTITION BY CARDTYPE ORDER BY CARID)) DIF
25 FROM CARD)
26 GROUP BY CARDTYPE, DIF) T2) T3
27 CONNECT BY PRIOR BEHINDS = FRONTS;CARDTYPE COUNTS LTRIM(FIRST_VALUE(SYS_CONNECT_
---------- ---------- --------------------------------------------------------------------------------
A 4 01001-01003,01007
B 2 05005-05006SQL>
CREATE TABLE t(cardtype VARCHAR2(10),numbers INT,cardid VARCHAR2(10));
INSERT INTO T VALUES ('A', 3, '01001');
INSERT INTO T VALUES ('A', 2, '01002');
INSERT INTO T VALUES ('A', 1, '01003');
INSERT INTO T VALUES ('A', 2, '01007');
INSERT INTO T VALUES ('B', 3, '05005');
INSERT INTO T VALUES ('B', 5, '05006');CREATE OR REPLACE FUNCTION MY_CONCAT(P_cardtype VARCHAR2) RETURN VARCHAR2 IS
V_STR VARCHAR2(2000) := '';
TYPE XX IS REF CURSOR;
R XX;
V_TEMP_STR VARCHAR2(100);
BEGIN
OPEN R FOR 'SELECT cardid FROM t WHERE cardtype=''' || P_cardtype||'''';
LOOP
FETCH R
INTO V_TEMP_STR;
EXIT WHEN R%NOTFOUND;
IF LENGTH(V_STR) > 0 THEN
V_STR := V_STR || ',' || V_TEMP_STR;
ELSE
V_STR := V_TEMP_STR;
END IF;
END LOOP;
CLOSE R;
RETURN(V_STR);
END MY_CONCAT;
/
SELECT cardtype,sum(numbers) sn,MY_CONCAT(cardtype) CARDID FROM t GROUP BY cardtype;
--或者(10g):
SELECT CARDTYPE,sum(numbers) sn,wmsys.wm_concat(CARDID) CARDID
FROM t GROUP BY cardtype;
/*
输出:
CARDTYPE SN CARDID
A 8 01001,01002,01003,01007
B 8 05005,05006
*/--一下是10g方法:
SELECT CARDTYPE, SUM(SN), WMSYS.WM_CONCAT(CARDID) CARDID
FROM (SELECT CARDTYPE,
SUM(NUMBERS) SN,
DECODE(TO_CHAR(MAX(CARDID)),
TO_CHAR(MIN(CARDID)),
TO_CHAR(MIN(CARDID)),
TO_CHAR(MIN(CARDID)) || '-' || TO_CHAR(MAX(CARDID))) CARDID
FROM (SELECT CARDTYPE, NUMBERS, CARDID, COL, COL - ROWNUM COL1
FROM (SELECT CARDTYPE, NUMBERS, CARDID, N COL
FROM (SELECT CARDTYPE,
NUMBERS,
CARDID,
TO_NUMBER(CARDID) N
FROM T)
ORDER BY CARDTYPE, N))
GROUP BY CARDTYPE, COL1
ORDER BY 1, 3)
GROUP BY CARDTYPE;/*输出:
CARDTYPE SUM(SN) CARDID
A 8 01001-01003,01007
B 8 05005-05006
*/
/*
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 * FROM SCOTT.CARD;CARDTYPE NUMBERS CARDID
---------- ---------- ----------
A 3 01001
A 2 01002
A 1 01003
A 2 01007
B 3 05005
B 5 050066 rows selectedSQL> 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-05006SQL>
SELECT CARDTYPE,wmsys.wm_concat(CARDID) CARDIDwmsys.wm_concat是什么函数？不明白啊
而且
《------- SELECT CARDTYPE,wmsys.wm_concat(CARDID) CARDID
FROM (SELECT CARDTYPE,
DECODE(TO_CHAR(MAX(CARDID)),
TO_CHAR(MIN(CARDID)),
TO_CHAR(MIN(CARDID)),
TO_CHAR(MIN(CARDID)) || '-' || TO_CHAR(MAX(CARDID))) CARDID
FROM (SELECT CARDTYPE, CARDID, COL, COL - ROWNUM COL1
FROM (SELECT CARDTYPE, CARDID, N COL
FROM (SELECT CARDTYPE, CARDID, TO_NUMBER(CARDID) N
FROM T)
ORDER BY CARDTYPE, N))
GROUP BY CARDTYPE, COL1
ORDER BY 1, 2)
GROUP BY CARDTYPE; 》
查出来的值还是 A 8 ,01001,01002,01003,01007
B 8 ,05006,05005 的形式啊
请指点！！！
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-05006SQL>
FROM (SELECT CARDTYPE,
SUM(NUMBERS) SN,
DECODE(TO_CHAR(MAX(CARDID)),
TO_CHAR(MIN(CARDID)),
TO_CHAR(MIN(CARDID)),
TO_CHAR(MIN(CARDID)) || '-' || TO_CHAR(MAX(CARDID))) CARDID
FROM (SELECT CARDTYPE,
NUMBERS,
CARDID,
TO_NUMBER(CARDID) -
(row_number()
over(partition by CARDTYPE order by CARDID)) col
FROM T
order by CARDTYPE, col)
GROUP BY CARDTYPE, COL
ORDER BY 1, 3)
GROUP BY CARDTYPE;本人测试了下,很好用,学到不少啊,呵~~