SQL递归查询示例

-- =============================================
-- Author:        zhaowei
-- Create date: 2012-7-23
-- Description:    获取指定分类下的产品(含子分类)
-- =============================================
ALTER PROCEDURE [dbo].[GetProductsByCategoryID]
    (
        @top int,
        @ProductCategoryID int
    )
AS
BEGIN
    SET NOCOUNT ON;
    with SCT(ElementID,[Name],ParentID,OrderNumber)
    as
    (
        select ElementID,[Name],ParentID,OrderNumber
        from Site_Category
        where ParentID=@ProductCategoryID
        union all
        select a.ElementID,a.[Name],a.ParentID,a.OrderNumber from Site_Category a
        inner join SCT b on a.ParentID=b.ElementID
    )
    
        select top(@top) * from Product where CategoryID in(
        select ElementID from SCT
        union all
        select ElementID from Site_Category where ElementID=@ProductCategoryID
    )    
END