关于使用CTE(公用表表达式)的递归查询

--关于使用CTE(公用表表达式)的递归查询
--CTE 的基本语法结构如下：
　WITH expression_name [ ( column_name [,...n] ) ]
　　　　AS
　　　　( CTE_query_definition )
　　　　--只有在查询定义中为所有结果列都提供了不同的名称时，列名称列表才是可选的。
　　　　--运行 CTE 的语句为：
　　　　SELECT <column_list> FROM expression_name;

 --CTE后面也可以跟其他的CTE，但只能使用一个with，多个CTE中间用逗号（,）分隔，如下面的SQL语句所示：
 WITH   cte1
          AS ( SELECT   *
               FROM     table1
               WHERE    name LIKE '测试%'
             ),
        cte2
          AS ( SELECT   *
               FROM     table2
               WHERE    id > 20
             ),
        cte3
          AS ( SELECT   *
               FROM     table3
               WHERE    price < 100
             )
    SELECT  a.*
    FROM    cte1 a ,
            cte2 b ,
            cte3 c
    WHERE   a.id = b.id
            AND a.id = c.id


--摘录：http://www.cnblogs.com/chengxiaohui/articles/2977104.html

 

 

--查询区域下子区域
WITH temp AS ( SELECT * FROM Regions WHERE Regions.ParentId ={ParentId} UNION ALL SELECT a.* FROM Regions a JOIN temp t ON a.ParentId = t.RegionId ) SELECT * FROM temp

 

private static IEnumerable<ClientEntity> GetClientsWidthParent(IDBAction db, string clientcode)
{
var sql = $@"WITH temp AS ( SELECT * FROM CRM_Client WHERE CRM_Client.ClientCode ='{clientcode}' UNION ALL SELECT a.* FROM CRM_Client a JOIN temp t ON t.FParentCode=a.ClientCode) SELECT * FROM temp WHERE FDelete=0 AND ISNULL(FAccountsFreeze,0)=0";
return db.View<ClientEntity>(sql);
}

private static IEnumerable<ClientEntity> GetClientsOfChildren(IDBAction db, string clientcode)
{
var sql = $@"WITH temp AS ( SELECT * FROM CRM_Client WHERE CRM_Client.FParentCode ='{clientcode}' UNION ALL SELECT a.* FROM CRM_Client a JOIN temp t ON a.FParentCode=t.ClientCode) SELECT * FROM temp WHERE FDelete=0 AND ISNULL(FAccountsFreeze,0)=0";
return db.View<ClientEntity>(sql);
}

SELECT DISTINCT ExchangeApplyId,STUFF((SELECT '；' + Product FROM (
SELECT ExchangeApplyId,FGoodsName+'(数量:'+CONVERT(NVARCHAR(100),ISNULL(SUM(Stock),0))+')' AS Product FROM [dbo].[CRM_ExchangeProduct] GROUP BY ExchangeApplyId,FGoodsName
) d WHERE ExchangeApplyId = T.ExchangeApplyId FOR XML PATH('')), 1, 1, '') AS Product FROM (
SELECT ExchangeApplyId,FGoodsName+'(数量:'+CONVERT(NVARCHAR(100),ISNULL(SUM(Stock),0))+')' AS Product FROM [dbo].[CRM_ExchangeProduct] GROUP BY ExchangeApplyId,FGoodsName
) AS T

 

 

 https://www.cnblogs.com/shy1766IT/p/6131586.html  SQL Server中的Stuff函数 FOR XML PATH 合并多行数据成一行, 显示部门、省市