--> 测试数据 if object_id('[TB]') is not null drop table [TB] GO create table [TB]([路径] varchar(20)) insert [TB] select 'A1 A2 A3' union all select 'B1 B2 B3'SELECT [路径编号]=RIGHT(flag,1), [路径]=left(flag,1) FROM( select flag=PARSENAME(REPLACE([路径],' ','.'),3) from [TB] UNION ALL select PARSENAME(REPLACE([路径],' ','.'),2) from [TB] UNION ALL select PARSENAME(REPLACE([路径],' ','.'),1) from [TB] )t ORDER BY 2,1/* 路径编号 路径 ---- ---- 1 A 2 A 3 A 1 B 2 B 3 B(6 行受影响)*/DROP TABLE TB
create table tb(col varchar(20)) insert into tb select 'A1 A2 A3' union all select 'B1 B2 B3'
--创建表值函数 create function f_name(@str varchar(20)) returns @tb table(no int Identity(1,1),col varchar(10)) as begin select @str=replace(@str,' ',',')+',' while charindex(',',@str)>1 begin insert into @tb (col) select left(@str,charindex(',',@str)-1) set @str=right(@str,len(@str)-charindex(',',@str)) end return end
--查询 select b.* from tb cross apply f_name(tb.col) b
/* no col ----------- ---------- 1 A1 2 A2 3 A3 1 B1 2 B2 3 B3(6 row(s) affected)
SELECT [路径编号]=RIGHT(flag,1), [路径]=flag FROM( select flag=PARSENAME(REPLACE([路径],' ','.'),3) from [TB] UNION ALL select PARSENAME(REPLACE([路径],' ','.'),2) from [TB] UNION ALL select PARSENAME(REPLACE([路径],' ','.'),1) from [TB] )t ORDER BY 2,1
谢谢啦~我貌似有点看不太懂~刚学SQL,好多语句都还没弄明白
拆着玩吧declare @t table ( 路径顺序号 int, 路径 varchar(30) )insert @t select 1,'A1 A2 A3' union all select 2,'B1 B2 B3' declare @s varchar(3000) declare @i int select @s = ISNULL(@s + ' union all select '+ CAST(路径顺序号 as varchar) +',''',' declare @i int declare @j int declare @t table ( 路径顺序号 int, 路径 varchar(30), i int ) insert @t(i,路径) select '+ CAST(路径顺序号 as varchar) +',''') + REPLACE(路径,' ',''' union all select '+ CAST(路径顺序号 as varchar) +',''') + '''' from @tset @s = @s + ' update @t set @j = case when @i = i then @j + 1 else 1 end ,@i = i ,路径顺序号 = @j select 路径顺序号,路径 from @t ' exec( @s)--结果 路径顺序号 路径 1 A1 2 A2 3 A3 1 B1 2 B2 3 B3
select left(@str,charindex(',',@str)-1) set @str=right(@str,len(@str)-charindex(',',@str)) 这两句都分别代表了什么意思啊?
if object_id('[TB]') is not null drop table [TB]
GO
create table [TB]([路径] varchar(20))
insert [TB]
select 'A1 A2 A3' union all
select 'B1 B2 B3'SELECT
[路径编号]=RIGHT(flag,1),
[路径]=left(flag,1) FROM(
select
flag=PARSENAME(REPLACE([路径],' ','.'),3)
from [TB]
UNION ALL
select
PARSENAME(REPLACE([路径],' ','.'),2)
from [TB]
UNION ALL
select
PARSENAME(REPLACE([路径],' ','.'),1)
from [TB]
)t
ORDER BY 2,1/*
路径编号 路径
---- ----
1 A
2 A
3 A
1 B
2 B
3 B(6 行受影响)*/DROP TABLE TB
insert into tb
select 'A1 A2 A3' union all
select 'B1 B2 B3'
--创建表值函数
create function f_name(@str varchar(20))
returns @tb table(no int Identity(1,1),col varchar(10))
as
begin
select @str=replace(@str,' ',',')+','
while charindex(',',@str)>1
begin
insert into @tb (col)
select left(@str,charindex(',',@str)-1)
set @str=right(@str,len(@str)-charindex(',',@str))
end
return
end
--查询
select b.* from tb cross apply f_name(tb.col) b
/*
no col
----------- ----------
1 A1
2 A2
3 A3
1 B1
2 B2
3 B3(6 row(s) affected)
SELECT
[路径编号]=RIGHT(flag,1),
[路径]=flag
FROM(
select
flag=PARSENAME(REPLACE([路径],' ','.'),3)
from [TB]
UNION ALL
select
PARSENAME(REPLACE([路径],' ','.'),2)
from [TB]
UNION ALL
select
PARSENAME(REPLACE([路径],' ','.'),1)
from [TB]
)t
ORDER BY 2,1
路径顺序号 int,
路径 varchar(30)
)insert @t select 1,'A1 A2 A3'
union all select 2,'B1 B2 B3'
declare @s varchar(3000)
declare @i int
select @s = ISNULL(@s + ' union all select '+ CAST(路径顺序号 as varchar) +',''','
declare @i int
declare @j int
declare @t table (
路径顺序号 int,
路径 varchar(30),
i int
)
insert @t(i,路径)
select '+ CAST(路径顺序号 as varchar) +',''') + REPLACE(路径,' ',''' union all select '+ CAST(路径顺序号 as varchar) +',''') + '''' from @tset @s = @s + '
update @t set
@j = case when @i = i then @j + 1 else 1 end
,@i = i
,路径顺序号 = @j
select 路径顺序号,路径 from @t
'
exec( @s)--结果
路径顺序号 路径
1 A1
2 A2
3 A3
1 B1
2 B2
3 B3
set @str=right(@str,len(@str)-charindex(',',@str))
这两句都分别代表了什么意思啊?