问题：锚点与列SQL Server 2005中的递归部分之间的类型不匹配

debugcn 发表于 Dev

汤玛士

我的表结构如下

ID   JID     EntryDate   RefundDate Comments     Refund ActionBy
---- ------- ----------- ---------- ------------ ------ --------
3    41986   2013-12-17  12/24/2013 Cancel       0      Matt
4    41986   2013-12-17  12/25/2013 done         1      Kelly
5    41986   2013-12-17  12/24/2013 no           0      Smith

我希望在发出sql后获得这种类型的输出

JID     EntryDate   RefundDate Comments     Refund ActionBy
------- ----------- ---------- ------------ ------ ---------
 41986
     2013-12-17  12/24/2013 Cancel       0      Matt
     2013-12-17  12/25/2013 done         1      Kelly
     2013-12-17  12/24/2013 no           0      Smith

 41987
     2013-12-17  12/24/2013 Cancel       0      Mosa
     2013-12-17  12/25/2013 done         1      Mike
     2013-12-17  12/24/2013 no           0      Jen


 41988
     2013-12-17  12/24/2013 Cancel       0      Ruby
     2013-12-17  12/25/2013 done         1      Kelly
     2013-12-17  12/24/2013 no           0      Simon

只是为了得到上面的输出，我如下发出了这个sql

;WITH  Hierarchy AS
(
    --  Anchor
    SELECT TOP 1  JID
            ,NULL EntryDate
            ,NULL RefundDate
            ,NULL Comments
            ,NULL Refund
            ,NULL ActionBy
            ,nLevel = 1
    FROM refundrequested

    UNION ALL
    --  Recursive query
    SELECT   E.JID
            ,E.EntryDate
            ,E.RefundDate
            ,E.Comments
            ,E.Refund
            ,E.ActionBy
            ,H.nLevel+1

    FROM refundrequested   E
    JOIN Hierarchy  H ON E.JID = H.JID
)

SELECT *
FROM Hierarchy

ORDER BY JID, nLevel

更新

;WITH  Hierarchy AS
(
    SELECT DISTINCT  JID
            ,CAST(NULL AS DATETIME) EntryDate
            ,CAST(NULL AS DATETIME) RefundDate
            ,CAST(NULL AS VARCHAR(MAX)) Comments
            ,CAST(NULL AS BIT) Refund
            ,CAST(NULL AS VARCHAR(30)) ActionBy
            ,nLevel = 1
    FROM refundrequested
    UNION ALL
    SELECT   CAST(NULL AS INT) JID
            ,E.EntryDate
            ,E.RefundDate
            ,E.Comments
            ,E.Refund
            ,E.ActionBy
            ,H.nLevel+1

    FROM refundrequested   E
    JOIN Hierarchy  H ON E.JID = H.JID
)

SELECT *
FROM Hierarchy
ORDER BY JID DESC, nLevel

现在得到此错误语句终止。在语句完成之前，最大递归100已用尽。

如何解决上述错误以及错误的含义是什么？...不清楚。

赌博严酷

解释你的输出。我没有得到nlevel的一部分。什么决定nlevel以及能持续多久？

;WITH  Hierarchy AS
(
    --  Anchor
    SELECT TOP 1  JID
            ,cast(NULL as date) EntryDate
            ,cast(NULL as date) RefundDate
            ,cast(NULL as varchar(10)) Comments
            ,cast(NULL as bit) Refund
            ,cast(NULL as varchar(10)) ActionBy
            ,nLevel = 1
    FROM @t

    UNION ALL
    --  Recursive query
    SELECT   E.JID
            ,E.EntryDate
            ,E.RefundDate
            ,E.Comments
            ,E.Refund
            ,E.ActionBy
            ,H.nLevel+1

    FROM @t   E
    JOIN Hierarchy  H ON E.JID = H.JID where h.nLevel<=3
)

select * from Hierarchy

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。