存储过程中如果递归匹配检索出大分类和子分类下的文章！哪位大哥帮忙下，万分感谢
/** * 根据符合条件查询文章数据 @vType 视图类别,1为大类文章视图vNewsMST,2为专题文章视图vSpecMST @SortID @SpecID @GroupsID @NewsTitle @CreateDateBegin @CreateDateEnd */ALTER PROCEDURE [dbo].[NewsListSelectByCondition] ( @vType int, @SortID varchar(12), @SpecID varchar(12), @GroupsID varchar(12), @NewsTitle varchar(120), @CreateDateBegin datetime, @CreateDateEnd datetime, @PageSize int, @CurrentPage int output, @TotalPage int output, @TotalRecord int output, @SortBy varchar(100) )AS--把所有单引号都替换成两个单引号SET @SortID = REPLACE(LTRIM(RTRIM(@SortID)),'''','''''')SET @SpecID = REPLACE(LTRIM(RTRIM(@SpecID)),'''','''''')SET @GroupsID = REPLACE(LTRIM(RTRIM(@GroupsID)),'''','''''')SET @NewsTitle = REPLACE(LTRIM(RTRIM(@NewsTitle)),'''','''''')SET @SortBy = REPLACE(LTRIM(RTRIM(@SortBy)),'''','''''')IF @SortBy=''BEGIN SET @SortBy='NewsID DESC'ENDDECLARE @Sql NVARCHAR(2000)DECLARE @SqlCount NVARCHAR(2000)DECLARE @WhereClause NVARCHAR(2000)--判断是专题文章还是系统文章,1为系统文章,2为专题文章IF @vType ='1'BEGINSET @Sql = 'SELECT *,ROW_NUMBER() OVER (ORDER BY ' + @SortBy + ') AS RowID FROM [dbo].[vNewsMST] 'SET @SqlCount = 'SELECT @Count=count(*) FROM [dbo].[vNewsMST] 'ENDELSE IF @vType ='2'BEGINSET @Sql = 'SELECT *,ROW_NUMBER() OVER (ORDER BY ' + @SortBy + ') AS RowID FROM [dbo].[vSpecMST] 'SET @SqlCount = 'SELECT @Count=count(*) FROM [dbo].[vSpecMST] 'ENDSET @WhereClause = 'WHERE DeleteFlag=0 'IF RTrim(@SortID)<>'' SET @WhereClause = @WhereClause + ' AND SortID=''' + @SortID + '''' SET @WhereClause = @WhereClause + ' OR SortID LIKE ''' + @SortID + '%'''IF RTrim(@SpecID)<>'' SET @WhereClause = @WhereClause + ' AND SpecID=''' + @SpecID + ''''IF RTrim(@GroupsID)<>'' SET @WhereClause = @WhereClause + ' AND GroupsID=''' + @GroupsID + ''''[color=#FF0000]IF RTrim(@NewsTitle)<>'' SET @WhereClause = @WhereClause + ' AND NewsTitle LIKE ''%' + @NewsTitle + '%'''[/color]IF RTrim(@CreateDateBegin)<>Cast('1900-1-1' AS DateTime) SET @WhereClause = @WhereClause + ' AND AddDate>=''' + Cast(@CreateDateBegin AS VARCHAR(30)) + ''''IF RTrim(@CreateDateEnd)<>Cast('1900-1-1' AS DateTime) SET @WhereClause = @WhereClause + ' AND AddDate<=''' + Cast(@CreateDateEnd AS VARCHAR(30)) + ''''SET @SqlCount = @SqlCount + @WhereClauseDECLARE @ParmDefinition nvarchar(100)SET @ParmDefinition = N'@Count int OUTPUT';EXECUTE sp_executesql @SqlCount, @ParmDefinition, @Count=@TotalRecord OUTPUT;SET @TotalPage=CEILING(CAST(@TotalRecord AS DECIMAL)/@PageSize)IF @CurrentPage > @TotalPage-1 SET @CurrentPage=@TotalPage-1IF @CurrentPage < 0 SET @CurrentPage=0DECLARE @_Start INT, @_End INTSET @_Start = ((@CurrentPage * @PageSize) + 1)SET @_End = (@_Start + @PageSize - 1)SET @Sql = 'SELECT * FROM (' + @Sql + @WhereClause + ') AS TempTable WHERE (RowID >= ' + CAST(@_Start AS VARCHAR(10)) + ') AND (RowID <= ' + CAST(@_End AS VARCHAR(10)) + ')'EXEC sp_executesql @SqlIF RTrim(@SortID)<>'' SET @WhereClause = @WhereClause + ' AND SortID=''' + @SortID + '''' SET @WhereClause = @WhereClause + ' OR SortID LIKE ''' + @SortID + '%'''
--BOM算法--产品配件清单查询示例(邹建)CREATE TABLE Item(ID int,Name varchar(10),Wast decimal(2,2))INSERT Item SELECT 1,N'A产品',0.01UNION ALL SELECT 2,N'B产品',0.02UNION ALL SELECT 3,N'C产品',0.10UNION ALL SELECT 4,N'D配件',0.15UNION ALL SELECT 5,N'E物料',0.03UNION ALL SELECT 6,N'F物料',0.01UNION ALL SELECT 7,N'G配件',0.02CREATE TABLE Bom(ItemID int,ChildId int)INSERT Bom SELECT 1,4UNION ALL SELECT 1,7 --A产品由D配件和G配件组成UNION ALL SELECT 2,1UNION ALL SELECT 2,6UNION ALL SELECT 2,7 --B产品由F物料及G配件组成UNION ALL SELECT 4,5UNION ALL SELECT 4,6 --D配件由F物料组成UNION ALL SELECT 3,2UNION ALL SELECT 3,1 --C产品由A产品和B产品组成GOCREATE FUNCTION f_Bom(@ItemIDs varchar(1000), --要查询物料清单及生产量的产品编号列表(逗号分隔)@Num int --要生产的数量)RETURNS @t TABLE(ItemID int,ChildId int,Nums int,Level int)ASBEGIN DECLARE @Level int SET @Level=1 INSERT @t SELECT a.ItemID,a.ChildId,ROUND(@Num/(1-b.Wast),0),@Level FROM Bom a,Item b WHERE a.ChildId=b.ID AND CHARINDEX(','+RTRIM(a.ItemID)+',',','+@ItemIDs+',')>0 WHILE @@ROWCOUNT>0 and @Level<140 BEGIN SET @Level=@Level+1 INSERT @t SELECT a.ItemID,b.ChildId,ROUND(a.Nums/(1-c.Wast),0),@Level FROM @t a,Bom b,Item c WHERE a.ChildId=b.ItemID AND b.ChildId=c.ID AND a.Level=@Level-1 END RETURNENDGO--调用函数展开产品1、2、3的结构及计算生产10个产品时,各需要多少个配件SELECT a.ItemID,ItemName=b.Name, a.ChildId,ChildName=c.Name, a.Nums,a.LevelFROM f_Bom('1,2,3',10) a,Item b,Item cWHERE a.ItemID=b.ID AND a.ChildId=c.IDORDER BY a.ItemID,a.Level,a.ChildId/*ItemID ItemName ChildId ChildName Nums Level----------- ---------- ----------- ---------- ----------- -----------1 A产品 4 D配件 12 11 A产品 7 G配件 10 11 A产品 5 E物料 12 21 A产品 6 F物料 12 22 B产品 1 A产品 10 12 B产品 6 F物料 10 12 B产品 7 G配件 10 12 B产品 4 D配件 12 22 B产品 7 G配件 10 22 B产品 5 E物料 12 32 B产品 6 F物料 12 33 C产品 1 A产品 10 13 C产品 2 B产品 10 13 C产品 1 A产品 10 23 C产品 4 D配件 12 23 C产品 6 F物料 10 23 C产品 7 G配件 10 23 C产品 7 G配件 10 23 C产品 4 D配件 12 33 C产品 5 E物料 12 33 C产品 6 F物料 12 33 C产品 7 G配件 10 33 C产品 5 E物料 12 43 C产品 6 F物料 12 4
[解决办法]
-->Title:Generating test data-->Author:wufeng4552-->Date :2009-09-30 08:52:38set nocount onif object_id('tb','U')is not null drop table tbgocreate table tb(ID int, ParentID int)insert into tb select 1,0 insert into tb select 2,1 insert into tb select 3,1 insert into tb select 4,2 insert into tb select 5,3 insert into tb select 6,5 insert into tb select 7,6-->Title:查找指定節點下的子結點if object_id('Uf_GetChildID')is not null drop function Uf_GetChildIDgocreate function Uf_GetChildID(@ParentID int)returns @t table(ID int)asbegin insert @t select ID from tb where ParentID=@ParentID while @@rowcount<>0 begin insert @t select a.ID from tb a inner join @t b on a.ParentID=b.id and not exists(select 1 from @t where id=a.id) end returnendgoselect * from dbo.Uf_GetChildID(5)/*ID-----------67*/-->Title:查找指定節點的所有父結點if object_id('Uf_GetParentID')is not null drop function Uf_GetParentIDgocreate function Uf_GetParentID(@ID int)returns @t table(ParentID int)asbegin insert @t select ParentID from tb where ID=@ID while @@rowcount!=0 begin insert @t select a.ParentID from tb a inner join @t b on a.id=b.ParentID and not exists(select 1 from @t where ParentID=a.ParentID) end returnendgoselect * from dbo.Uf_GetParentID(2)/*ParentID-----------10*/本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/wufeng4552/archive/2009/09/30/4619995.aspx
[解决办法]
-->Title:Generating test data
-->Author:wufeng4552
-->Date :2009-09-30 08:52:38
set nocount on
if object_id('tb','U')is not null drop table tb
go
create table tb(ID int, ParentID int)
insert into tb select 1,0
insert into tb select 2,1
insert into tb select 3,1
insert into tb select 4,2
insert into tb select 5,3
insert into tb select 6,5
insert into tb select 7,6
-->Title:查找指定節點下的子結點
if object_id('Uf_GetChildID')is not null drop function Uf_GetChildID
go
create function Uf_GetChildID(@ParentID int)
returns @t table(ID int)
as
begin
insert @t select ID from tb where ParentID=@ParentID
while @@rowcount<>0
begin
insert @t select a.ID from tb a inner join @t b
on a.ParentID=b.id and
not exists(select 1 from @t where id=a.id)
end
return
end
go
select * from dbo.Uf_GetChildID(5)
/*
ID
-----------
6
7
*/
-->Title:查找指定節點的所有父結點
if object_id('Uf_GetParentID')is not null drop function Uf_GetParentID
go
create function Uf_GetParentID(@ID int)
returns @t table(ParentID int)
as
begin
insert @t select ParentID from tb where ID=@ID
while @@rowcount!=0
begin
insert @t select a.ParentID from tb a inner join @t b
on a.id=b.ParentID and
not exists(select 1 from @t where ParentID=a.ParentID)
end
return
end
go
select * from dbo.Uf_GetParentID(2)
/*
ParentID
-----------
1
0
*/