求教一个合并更新的高效写法

gll30 2009-09-26 10:14:27

原始数据结构如下：
表1：
customerid tel quota
1 123 3
1 123 4
2 234 6
3 432 7

================================

希望能有一套高效的方法，将其迅速整理成如下格式：
表2
customerid tel quota
1 123 3,4
2 234 6
3 432 7

也就是以customerid和tel作为联合主键，将分散在表1中零碎的信息，都整合在一起，输入到表2

我尝试过很多方法，比如，写嵌套for更新循环，但是效率似乎很低，排序很慢。因为表1中的数据超过10万行。请问，大家有什么比较好的算法呢？

因为表1

...全文

578 2 打赏收藏转发到动态举报

写回复

用AI写文章

2 条回复

切换为时间正序

请发表友善的回复…

发表回复

liyan93834183 2009-09-27

打赏
举报

这有个例子，你看一下

CREATE TABLE T_BASE(ID INT,NAME VARCHAR(10),AGE INT); 



INSET INTO T_BASE VALUES 

(1,'ZHANG',30), 

(2,'YANG',28), 

(3,'LI',33); 

CREATE TABLE T_DETAIL(ID INT,INFO VARCHAR(10)); 

INSERT INTO T_DETAIL VALUES 

(1,'ZHANG1'), 

(1,'ZHANG2'), 

(1,'ZHANG3'), 

(2,'YANG1'), 

(2,'YANG2'), 

(3,'LI1'); 



WITH B (FATHER,SON,ID,ALLINFO) AS 

    (SELECT  RTRIM(LTRIM(CHAR(A.ID)))|| CHAR(ROW_NUMBER() OVER(PARTITION BY ID)) AS FATHER, 

            RTRIM(LTRIM(CHAR(A.ID)))|| CHAR(ROW_NUMBER() OVER(PARTITION BY ID) + 1) AS SON, 

            A.ID, CAST(A.INFO AS VARCHAR(100)) 

    FROM T_DETAIL A 

    UNION ALL 

    SELECT C.FATHER,C.SON,C.ID, 

    RTRIM(LTRIM(B.ALLINFO))||','||RTRIM(LTRIM(CHAR(C.INFO))) 

    FROM (SELECT  

    RTRIM(LTRIM(CHAR(A.ID)))|| CHAR(ROW_NUMBER() OVER(PARTITION BY ID)) AS FATHER, 

    RTRIM(LTRIM(CHAR(A.ID)))|| CHAR(ROW_NUMBER() OVER(PARTITION BY ID) + 1) AS SON, 

        A.ID, A.INFO FROM T_DETAIL A) AS C, B 

    WHERE B.SON= C.FATHER) 

SELECT  E.ID,E.NAME,E.AGE,D.ALLINFO 

FROM (SELECT ROW_NUMBER() OVER(PARTITION BY ID ORDER BY LENGTH(ALLINFO) DESC) AS ROW_NUM, B.ID,B.ALLINFO FROM B) AS D,T_BASE E 

WHERE D.ROW_NUM = 1 AND D.ID=E.ID;