select * from 表a where aArticleChannel = 'C2,C107,T15'select top 10 * from 表b where aKey in (select aKey from 表a where aArticleChannel = 'C2,C107,T15') union select top 10 * from 表b where aKeyParentIDin (select aKeyParentIDfrom 表a where aArticleChannel = 'C2,C107,T15') union select top 10 * from 表b where aKeyParentPath (select aKeyParentPath 表a where aArticleChannel = 'C2,C107,T15')以上是我瞎猜的結果,嘿嘿...
--这样查询 declare @id varchar(10) set @id='C107' --查询C107及其所有子类--查询 select top 8000 id=identity(int) into #t from syscolumns a,syscolumns bselect a.* from 表A a,( --要解子类的id列表 select distinct id=','+substring(a.aKeyParentPath,b.id,charindex(',',a.aKeyParentPath+',',b.id)-b.id)+',' from 表B a,#t b where charindex(','+@id+',',','+a.aKeyParentPath+',')>0 and len(a.aKeyParentPath)>=b.id and substring(','+a.aKeyParentPath,b.id,1)=',' ) b where charindex(','+@id+',',','+a.Channel+',')>0 --C107的所有文章 or charindex(b.id,','+a.Channel+',')>0 --C107的所有子类文章 drop table #t
--或者写成存储过程 create proc p_qry @id varchar(10) --查询的Channel as set nocount on --查询 select top 8000 id=identity(int) into #t from syscolumns a,syscolumns bselect a.* from 表A a,( --要解子类的id列表 select distinct id=','+substring(a.aKeyParentPath,b.id,charindex(',',a.aKeyParentPath+',',b.id)-b.id)+',' from 表B a,#t b where charindex(','+@id+',',','+a.aKeyParentPath+',')>0 and len(a.aKeyParentPath)>=b.id and substring(','+a.aKeyParentPath,b.id,1)=',' ) b where charindex(','+@id+',',','+a.Channel+',')>0 --C107的所有文章 or charindex(b.id,','+a.Channel+',')>0 --C107的所有子类文章 go--调用 exec p_qry 'C107'
--或者写成存储过程 create proc p_qry @id varchar(10) --查询的Channel as set nocount on --查询 select top 8000 id=identity(int) into #t from syscolumns a,syscolumns bselect distinct a.* from 表A a,( --要解子类的id列表 select distinct id=','+substring(a.aKeyParentPath,b.id,charindex(',',a.aKeyParentPath+',',b.id)-b.id)+',' from 表B a,#t b where charindex(','+@id+',',','+a.aKeyParentPath+',')>0 and len(a.aKeyParentPath)>=b.id and substring(','+a.aKeyParentPath,b.id,1)=',' ) b where charindex(','+@id+',',','+a.Channel+',')>0 --C107的所有文章 or charindex(b.id,','+a.Channel+',')>0 --C107的所有子类文章 order by a.aArticleDate desc go--调用 exec p_qry 'C107'
union
select top 10 * from 表b where aKeyParentIDin (select aKeyParentIDfrom 表a where aArticleChannel = 'C2,C107,T15')
union
select top 10 * from 表b where aKeyParentPath (select aKeyParentPath 表a where aArticleChannel = 'C2,C107,T15')以上是我瞎猜的結果,嘿嘿...
declare @id varchar(10)
set @id='C107' --查询C107及其所有子类--查询
select top 8000 id=identity(int) into #t from syscolumns a,syscolumns bselect a.*
from 表A a,(
--要解子类的id列表
select distinct id=','+substring(a.aKeyParentPath,b.id,charindex(',',a.aKeyParentPath+',',b.id)-b.id)+','
from 表B a,#t b
where charindex(','+@id+',',','+a.aKeyParentPath+',')>0
and len(a.aKeyParentPath)>=b.id
and substring(','+a.aKeyParentPath,b.id,1)=','
) b where charindex(','+@id+',',','+a.Channel+',')>0 --C107的所有文章
or charindex(b.id,','+a.Channel+',')>0 --C107的所有子类文章
drop table #t
--或者写成存储过程
create proc p_qry
@id varchar(10) --查询的Channel
as
set nocount on
--查询
select top 8000 id=identity(int) into #t from syscolumns a,syscolumns bselect a.*
from 表A a,(
--要解子类的id列表
select distinct id=','+substring(a.aKeyParentPath,b.id,charindex(',',a.aKeyParentPath+',',b.id)-b.id)+','
from 表B a,#t b
where charindex(','+@id+',',','+a.aKeyParentPath+',')>0
and len(a.aKeyParentPath)>=b.id
and substring(','+a.aKeyParentPath,b.id,1)=','
) b where charindex(','+@id+',',','+a.Channel+',')>0 --C107的所有文章
or charindex(b.id,','+a.Channel+',')>0 --C107的所有子类文章
go--调用
exec p_qry 'C107'
--或者写成存储过程
create proc p_qry
@id varchar(10) --查询的Channel
as
set nocount on
--查询
select top 8000 id=identity(int) into #t from syscolumns a,syscolumns bselect distinct a.*
from 表A a,(
--要解子类的id列表
select distinct id=','+substring(a.aKeyParentPath,b.id,charindex(',',a.aKeyParentPath+',',b.id)-b.id)+','
from 表B a,#t b
where charindex(','+@id+',',','+a.aKeyParentPath+',')>0
and len(a.aKeyParentPath)>=b.id
and substring(','+a.aKeyParentPath,b.id,1)=','
) b where charindex(','+@id+',',','+a.Channel+',')>0 --C107的所有文章
or charindex(b.id,','+a.Channel+',')>0 --C107的所有子类文章
order by a.aArticleDate desc
go--调用
exec p_qry 'C107'