表ID 行ID 内容1 0 ABCD@ERT
1 1 DFG@HJK
2 0 BNM@SDF
3 0 ASD@ZXC
3 1 QWE@YUI例表如上,希望得的显示结果为:表ID 行ID 内容 序号ID1 0 ABCD 1
1 0 ERT 2
1 1 DFG 1
1 1 HJK 2
2 0 BNM 1
2 0 SDF 2
3 0 ASD 1
3 0 ZXC 2
3 1 QWE 1
3 1 YUI 2就是在查找内容时,发现内容有@这个字符的就把内容拆分,然后再给别创建一个序号ID。想了很久都没做出来,这样的循环能做吗,还往高手指点下。
1 1 DFG@HJK
2 0 BNM@SDF
3 0 ASD@ZXC
3 1 QWE@YUI例表如上,希望得的显示结果为:表ID 行ID 内容 序号ID1 0 ABCD 1
1 0 ERT 2
1 1 DFG 1
1 1 HJK 2
2 0 BNM 1
2 0 SDF 2
3 0 ASD 1
3 0 ZXC 2
3 1 QWE 1
3 1 YUI 2就是在查找内容时,发现内容有@这个字符的就把内容拆分,然后再给别创建一个序号ID。想了很久都没做出来,这样的循环能做吗,还往高手指点下。
go
insert into tb select 1, 0, 'ABCD@ERT'
insert into tb select 1, 1, 'DFG@HJK'
insert into tb select 2, 0, 'BNM@SDF'
insert into tb select 3, 0, 'ASD@ZXC'
insert into tb select 3,1, 'QWE@YUI'
goselect a, b, case when charindex('@', c) > 0 then left(c, charindex('@', c) - 1) else c end as c, 1 as d from tb
union all
select a, b, substring(c, charindex('@', c) + 1, 20) as c, 2 as d from tb where charindex('@', c) > 0
order by a, b
select 表ID,行ID,
内容=Case when charindex('@',内容)>0 then left(内容,charindex('@',内容)-1) else 内容 end,
序号ID=row_number()over(partition by 表ID,行ID order by 内容)
from 表
create table #tb(a int, b int, c varchar(20))
go
insert into #tb select 1, 0, 'ABCD@ERT'
insert into #tb select 1, 1, 'DFG@HJK'
insert into #tb select 2, 0, 'BNM@SDF'
insert into #tb select 3, 0, 'ASD@ZXC'
insert into #tb select 3,1, 'QWE@YUI'select * from #tbselect a,b,PARSENAME(replace(c,'@','...'),4) as 内容,1as 序号ID from #tb
union
select a,b,PARSENAME(replace(c,'@','...'),1) as 内容,2as 序号ID from #tb