--查询 select 商品名称,系列号=substring(a.系列号,b.id,charindex(',',a.系列号+',',b.id)-b.id) from 进仓表 a,( select id=a.id+b.id+1 from( select id=0 union all select 1 union all select id=2 union all select 3 union all select id=4 union all select 5 union all select id=6 union all select 7 union all select id=8 union all select 9 ) a,( select id=0 union all select 10 union all select id=20 union all select 30 union all select id=40 union all select 50 union all select id=60 union all select 70 union all select id=80 union all select 90 ) b )b where substring(','+a.系列号,b.id,1)=','
--测试--测试数据 create table 进仓表(商品名称 varchar(10),系列号 varchar(30)) insert 进仓表 select 'N3210','SN11111,SN22222,SN33' --字段值改一下,后面不要多一个, union all select 'N8880','SN5555,SN6666' go--查询 select 商品名称,系列号=substring(a.系列号,b.id,charindex(',',a.系列号+',',b.id)-b.id) from 进仓表 a,( select id=a.id+b.id+1 from( select id=0 union all select 1 union all select id=2 union all select 3 union all select id=4 union all select 5 union all select id=6 union all select 7 union all select id=8 union all select 9 ) a,( select id=0 union all select 10 union all select id=20 union all select 30 union all select id=40 union all select 50 union all select id=60 union all select 70 union all select id=80 union all select 90 ) b )b where substring(','+a.系列号,b.id,1)=',' go--删除测试 drop table 进仓表/*--测试结果商品名称 系列号 ---------- ------------------------------ N3210 SN11111 N3210 SN22222 N3210 SN33 N8880 SN5555 N8880 SN6666(所影响的行数为 5 行) --*/
--用序数表的处理方法--创建序数表 if exists (select * from dbo.sysobjects where id = object_id(N'[序数表]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [序数表] GO--为了效率,所以要一个辅助表配合 select top 1000 id=identity(int,1,1) into 序数表 from syscolumns a,syscolumns b alter table 序数表 add constraint pk_id_序数表 primary key(id) go --测试--测试数据 create table 进仓表(商品名称 varchar(10),系列号 varchar(30)) insert 进仓表 select 'N3210','SN11111,SN22222,SN33' union all select 'N8880','SN5555,SN6666' go--查询 select 商品名称,系列号=substring(a.系列号,b.id,charindex(',',a.系列号+',',b.id)-b.id) from 进仓表 a,序数表 b where substring(','+a.系列号,b.id,1)=',' go--删除测试 drop table 进仓表/*--测试结果商品名称 系列号 ---------- ------------------------------ N3210 SN11111 N3210 SN22222 N3210 SN33 N8880 SN5555 N8880 SN6666(所影响的行数为 5 行) --*/
函数看来不适合,还是要用存储过程: (注意数据量太多做不了)DECLARE @SQL VARCHAR(8000) SET @SQL='' SELECT @SQL=@SQL+'SELECT 商品名称='''+商品名称+''',系列号='''+REPLACE(LEFT(系列号,LEN(系列号)-1),',',''' UNION ALL SELECT 商品名称='''+商品名称+''',系列号=''')+''' UNION ALL ' FROM 进仓表 SET @SQL=LEFT(@SQL,LEN(@SQL)-10) EXEC(@SQL)
select 商品名称,系列号=substring(a.系列号,b.id,charindex(',',a.系列号+',',b.id)-b.id)
from 进仓表 a,(
select id=a.id+b.id+1
from(
select id=0 union all select 1
union all select id=2 union all select 3
union all select id=4 union all select 5
union all select id=6 union all select 7
union all select id=8 union all select 9
) a,(
select id=0 union all select 10
union all select id=20 union all select 30
union all select id=40 union all select 50
union all select id=60 union all select 70
union all select id=80 union all select 90
) b
)b where substring(','+a.系列号,b.id,1)=','
create table 进仓表(商品名称 varchar(10),系列号 varchar(30))
insert 进仓表 select 'N3210','SN11111,SN22222,SN33' --字段值改一下,后面不要多一个,
union all select 'N8880','SN5555,SN6666'
go--查询
select 商品名称,系列号=substring(a.系列号,b.id,charindex(',',a.系列号+',',b.id)-b.id)
from 进仓表 a,(
select id=a.id+b.id+1
from(
select id=0 union all select 1
union all select id=2 union all select 3
union all select id=4 union all select 5
union all select id=6 union all select 7
union all select id=8 union all select 9
) a,(
select id=0 union all select 10
union all select id=20 union all select 30
union all select id=40 union all select 50
union all select id=60 union all select 70
union all select id=80 union all select 90
) b
)b where substring(','+a.系列号,b.id,1)=','
go--删除测试
drop table 进仓表/*--测试结果商品名称 系列号
---------- ------------------------------
N3210 SN11111
N3210 SN22222
N3210 SN33
N8880 SN5555
N8880 SN6666(所影响的行数为 5 行)
--*/
if exists (select * from dbo.sysobjects where id = object_id(N'[序数表]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [序数表]
GO--为了效率,所以要一个辅助表配合
select top 1000 id=identity(int,1,1) into 序数表
from syscolumns a,syscolumns b
alter table 序数表 add constraint pk_id_序数表 primary key(id)
go
--测试--测试数据
create table 进仓表(商品名称 varchar(10),系列号 varchar(30))
insert 进仓表 select 'N3210','SN11111,SN22222,SN33'
union all select 'N8880','SN5555,SN6666'
go--查询
select 商品名称,系列号=substring(a.系列号,b.id,charindex(',',a.系列号+',',b.id)-b.id)
from 进仓表 a,序数表 b where substring(','+a.系列号,b.id,1)=','
go--删除测试
drop table 进仓表/*--测试结果商品名称 系列号
---------- ------------------------------
N3210 SN11111
N3210 SN22222
N3210 SN33
N8880 SN5555
N8880 SN6666(所影响的行数为 5 行)
--*/
(注意数据量太多做不了)DECLARE @SQL VARCHAR(8000)
SET @SQL=''
SELECT @SQL=@SQL+'SELECT 商品名称='''+商品名称+''',系列号='''+REPLACE(LEFT(系列号,LEN(系列号)-1),',',''' UNION ALL SELECT 商品名称='''+商品名称+''',系列号=''')+''' UNION ALL '
FROM 进仓表
SET @SQL=LEFT(@SQL,LEN(@SQL)-10)
EXEC(@SQL)