CTE~

最新推荐文章于 2024-08-13 15:40:03 发布

zenowolf

最新推荐文章于 2024-08-13 15:40:03 发布

阅读量449

点赞数

文章标签： join path

本文链接：https://blog.csdn.net/raineo/article/details/4438226

版权

;WITH TEMP (GROUP_ID,GROUP_NAME,PARENT_GROUP_ID,GROUP_MEMO,VIEW_SORT,TYPE_KBN,CF_NAME)
AS
(
SELECT GROUP_ID
,GROUP_NAME
,PARENT_GROUP_ID
,GROUP_MEMO
,VIEW_SORT
,TYPE_KBN
, CASE WHEN ISNULL(C.CUSTOMER_NAME,'')<>'' THEN C.CUSTOMER_NAME
         WHEN ISNULL(D.FACTORY_NAME,'') <>'' THEN D.FACTORY_NAME
         ELSE '' END CF_NAME
FROM dbo.FILE_SHARE_GROUP_DATA A
  LEFT JOIN dbo.CUSTOMER C
ON A.GROUP_NAME=C.CUSTOMER_ID AND C.DEL_FLG='0'
LEFT JOIN dbo.FACTORY D
ON A.GROUP_NAME=D.FACTORY_ID AND D.DEL_FLG='0'
),
RAINEO(GROUP_ID,GROUP_NAME,PARENT_GROUP_ID,VIEW_SORT,TYPE_KBN,path)
AS
(
                SELECT GROUP_ID,GROUP_NAME,PARENT_GROUP_ID,VIEW_SORT,TYPE_KBN
                       ,path=CONVERT(VARCHAR(MAX),GROUP_NAME+'/')
                FROM TEMP A
                WHERE NOT EXISTS
                      (
                          SELECT *
                          FROM dbo.FILE_SHARE_GROUP_DATA
                          WHERE GROUP_ID=A.PARENT_GROUP_ID
                      )
                UNION ALL
                SELECT
                    A.GROUP_ID,A.GROUP_NAME,A.PARENT_GROUP_ID,A.VIEW_SORT,A.TYPE_KBN
                    ,CASE WHEN EXISTS (
         SELECT T.GROUP_ID
         FROM dbo.FILE_SHARE_GROUP_DATA T
         WHERE PARENT_GROUP_ID IN ('FG000000','FG000002') AND A.GROUP_ID=T.GROUP_ID
          )
        THEN CONVERT(VARCHAR(MAX),B.path+A.CF_NAME+N'('+A.GROUP_NAME+N')'+'/')
        ELSE CONVERT(VARCHAR(MAX),B.path+A.GROUP_NAME+'/')
                          END path
                FROM TEMP A
                    JOIN RAINEO AS B
                ON A.PARENT_GROUP_ID=B.GROUP_ID
            )SELECT * FROM RAINEO