SQL 递归查询

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;

 

posted @ 2013-12-19 19:21  沙耶  阅读(384)  评论(0编辑  收藏  举报