27,579
社区成员
发帖
与我相关
我的任务
分享
select CI_ID from TE_CorpInfoReal where C_ChildID in(select C_ChildID from TE_CorpInfoReal where CI_ID=@CI_ID)
create table tb(CI_ID int,C_ChildID nvarchar(100))
insert tb
select '10024', '1045,1048,1056,1058,1063,'
union all select '10025', '1045,1048,1056,1058,1063,'
union all select '10026', '1045,'
union all select '10027', '1048,'
union all select '10028', '1056,1058,'
union all select '10029', '1111,'
go
select *
from tb a
left join tb b on a.CI_ID <> b.CI_ID and
a.C_ChildID like '%' + b.C_ChildID + '%'
--where a.CI_ID = '10024'
go
drop table tb
/*
Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) Jul 9 2008 14:43:34 Copyright (c)
1988-2008 Microsoft Corporation Enterprise Evaluation Edition on Windows NT 5.1 <X86>
(Build 2600: Service Pack 3)
愿和大家共同进步
如有雷同、实属巧合
●●●●●2009-09-08 14:29:01.607●●●●●
★★★★★soft_wsx★★★★★
*/
if OBJECT_ID('tb') is not null drop table tb
go
create table tb(CI_ID int,C_ChildID nvarchar(100))
insert tb
select '10024', '1045,1048,1056,1058,1063,'
union all select '10025', '1045,1048,1056,1058,1063,'
union all select '10026', '1045,'
union all select '10027', '1048,'
union all select '10028', '1056,1058,'
union all select '10029', '1111,'
--用临时表将数据拆分为表
declare @max int
select @max=len(c_childid) from tb order by 1
set ROWCOUNT @max
select IDENTITY(int,1,1) as id,COL=1 into #a from dbo.syscolumns a,dbo.syscolumns b --drop table #a
set ROWCOUNT 0
select a.CI_ID,SUBSTRING(a.C_ChildID,b.id,CHARINDEX(',',a.c_childid+',',b.id)-b.id) as childid
from tb a,#a b
where LEN(a.C_ChildID)>=b.id
and SUBSTRING(','+a.C_ChildID,b.id,1)=','
/*
CI_ID childid
10024 1045
10024 1048
10024 1056
10024 1058
10024 1063
10025 1045
10025 1048
10025 1056
10025 1058
10025 1063
10026 1045
10027 1048
10028 1056
10028 1058
10029 1111
*/