参考大力的一个程序修改成:
select top 8000 identity(int,1,1) id into #t from sysobjects a,sysobjects bselect code from (
select Code from (
select
substring('/'+Code,id+1,charindex('/','/'+Code+'/',id+1)-id-1) Code
from baseinfo A, #t B
where substring('/'+Code,id,8000) like '/_%') C
group by Code ) D where code not in (select code from otherinfo)
go
drop table #t
select top 8000 identity(int,1,1) id into #t from sysobjects a,sysobjects bselect code from (
select Code from (
select
substring('/'+Code,id+1,charindex('/','/'+Code+'/',id+1)-id-1) Code
from baseinfo A, #t B
where substring('/'+Code,id,8000) like '/_%') C
group by Code ) D where code not in (select code from otherinfo)
go
drop table #t
解决方案 »
- 推荐一本学习MS Sqlserver 2008 的图书
- 怎样把一列的数据转换为多列的数据?
- 【求助!】SQL 2000 安装问题
- 资源池 'default' 没有足够的系统内存来运行此查询。
- SQL2005的备份问题
- oledb 读取excel substing()?
- 一个算法问题,求好方法
- 我装了SP4之后,为什么显示的还是SP1?
- 客户端为什么连不上服务器?
- 能否 使用 形象 的 比喻 例子 来 说明 合并连接 ,hash 连接 ,loop 连接 REMOTE 连接? 200 分 !!!
- 菜鸟问题,insert into与insert有什么区别?
- update iteminf set (select image1 from iteminf)=(select image1 from iteminf2 where model_no=iteminf.model_no)
--N要连继,
select top 8000 identity(int,1,1) as N into numtab from
(select top 100 id=1 from sysobjects) as a,
(select top 100 id=1 from sysobjects) as b,
(select top 100 id=1 from sysobjects) as c---------------------------------------------------------------------
declare @a table (id int,string varchar(8000))
insert @a select 1 ,'a,b,c,sd,dfsdfg'
union select 2, 'a,n,sdf,we,t'
union select 3, 's,df,df'select a.*,b.*,id,substring(','+string+',',N+1,charindex(',',','+string+',',N+1)-(N+1))
from @a a,numtab b
where substring(','+string+',',N,8000) like ',_%'
order by id,N
declare @i int
select @i=1
select @count=(select count(Code) from OtherInfo order by code)
declare @code as varchar(100)
declare @sql as varchar(1000)
while @i<=@count
select @sql='select top 1 code from OtherInfo where code not in(select top '+ cast(@i-1 as varchar(100))+' code from OtherInfo order by code) order by code'
select @code=exec(@sql)
select * from BaseInfo into #tem where Code like '%@code%'
select @i=@i+1
loop
select distinct id from #tem我的思路是先计算出OtherInfo表记录个数
再利用循环来处理数据
select @sql='select top 1 code from OtherInfo where code not in(select top '+ cast(@i-1 as varchar(100))+' code from OtherInfo order by code) order by code'
这一句是提取OtherInfo表的每一行,先取第一行,再取第2行,利用循环一直下去
把符合记录的数据插入到临时表#tem里面
再从临时表里面找出你要的id
insert #baseinfo values(1,'/123/456/')
insert #baseinfo values(2,'/133/244/789/')
insert #baseinfo values(3,'/376/963/')create table #OtherInfo (code varchar(100))
insert #OtherInfo values(123)
insert #OtherInfo values(789)
insert #OtherInfo values(244)---开始select top 8000 identity(int,1,1) id into #dali from sysobjects a,sysobjects bselect * from (select aa.id,substring(aa.code,bb.id+1,charindex('/',aa.code,bb.id+1)-bb.id-1) code from #baseinfo aa,#dali bb where substring(aa.code,bb.id,8000) like '/_%') tem where code not in (select code from #OtherInfo) order by id
go
drop table #OtherInfo,#baseinfo,#dali
select aa.id,
substring(aa.code,bb.id+1,charindex('/',aa.code,bb.id+1)-bb.id-1) code
from #baseinfo aa,#dali bb where substring(aa.code,bb.id,8000) like '/_%'
into tb1
from (
select 'aaa/bbb/ccc/ddd/eee/fff' as code
union all
select 'aaa/bbb/ccc/ddd/eee/fff' as code
union all
select 'aaa/bbb/ccc/ddd/eee/fff' as code
union all
select 'yyy/mmm/nnn/bbb/vvv/ccc' as code
union all
select 'ggg/uuu/iii/ooo' as code) t1select * from tb1
select code
into tb2
from (
select 'aaa' as code
union all
select 'bbb' as code
union all
select 'ccc' as code
union all
select 'ddd' as code) t1select * from tb2
--将TB1的数据按'/'转换为纵行的数据
while exists(select * from tb1 where charindex('/',code) > 0 )
begin
--插入反斜线左边的部分
insert into tb1 (code)
select left(code,charindex('/',code)-1) from tb1
where charindex('/',code) > 0
--去掉反斜线左边的部分
update tb1 set
code = ltrim(right(code,len(code) - charindex('/',code)))
where charindex('/',code) > 0
endselect * from tb1--查询--结果
select *
from tb1
where not exists(select * from tb2 where tb1.code = tb2.code)rollback tran