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