--try create table #tb( dt datetime ) declare @d varchar(8000) set @d='1#;2#;5#;16#' select @d=replace(@d+';',';',''''+' union all select '+'''') select @d='insert #tb select '+''''+left(@d,len(@d)-18) --print @d exec(@d) select * from #tb /* 1# 2# 5# 16# */
--> 测试数据:[tb] IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb] GO CREATE TABLE [tb]([id] INT,[name] VARCHAR(50)) INSERT [tb] SELECT 1,'1#;2#;5#;16#' --------------开始查询--------------------------SELECT [id], b.name FROM ( SELECT [id] , [value] = CONVERT(XML, '<v>' + REPLACE([name], ';', '</v><v>') + '</v>') FROM [tb] ) A OUTER APPLY ( SELECT name = N.v.value ('.', 'varchar(50)') FROM A.[value].nodes ('/v') N ( v) ) B ----------------结果---------------------------- /* 1 1# 1 2# 1 5# 1 16# */
--> 测试数据: [test1] if object_id('[test]') is not null drop table [test] create table [test1] (id int,type int,name varchar(1)) insert into [test1] select 1,1,'a' union all select 2,1,'b' union all select 3,1,'c' union all select 4,1,'d' union all select 5,2,'e' union all select 6,2,'f' go with wsp as ( select *,no=(select COUNT(1) from test1 where type=a.type and id<=a.id) from [test1] a ) select a.id,a.type,a.name,b.id,b.type,b.name from wsp a left join wsp b on a.no=b.no and a.id!=b.id where a.type=1
--怎么发了这么多帖子。 --> 测试数据: [JIT-mucus] create table [JIT-mucus] (id int,machinenum varchar(100)) insert into [JIT-mucus] select 1,'1#;2#;5#;16#' union all select 2,'3#;4#' goselect id,substring(machinenum+';',b.number,charindex(';',machinenum+';',b.number+1)-b.number) X from [JIT-mucus] a,master..spt_values b where b.type='p' and b.number between 1 and 100 and substring(';'+machinenum,b.number,1)=';'
--try
create table #tb( dt datetime )
declare @d varchar(8000)
set @d='1#;2#;5#;16#'
select @d=replace(@d+';',';',''''+' union all select '+'''')
select @d='insert #tb select '+''''+left(@d,len(@d)-18)
--print @d
exec(@d)
select * from #tb
/*
1#
2#
5#
16#
*/
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO
CREATE TABLE [tb]([id] INT,[name] VARCHAR(50))
INSERT [tb]
SELECT 1,'1#;2#;5#;16#'
--------------开始查询--------------------------SELECT [id], b.name
FROM (
SELECT [id] ,
[value] = CONVERT(XML, '<v>' + REPLACE([name], ';', '</v><v>') + '</v>')
FROM [tb]
) A
OUTER APPLY (
SELECT name = N.v.value ('.', 'varchar(50)') FROM A.[value].nodes ('/v') N ( v)
) B
----------------结果----------------------------
/*
1 1#
1 2#
1 5#
1 16#
*/
if object_id('[test]') is not null drop table [test]
create table [test1] (id int,type int,name varchar(1))
insert into [test1]
select 1,1,'a' union all
select 2,1,'b' union all
select 3,1,'c' union all
select 4,1,'d' union all
select 5,2,'e' union all
select 6,2,'f'
go
with wsp
as
(
select *,no=(select COUNT(1) from test1 where type=a.type and id<=a.id) from [test1] a
)
select a.id,a.type,a.name,b.id,b.type,b.name from wsp a left join wsp b
on a.no=b.no and a.id!=b.id
where a.type=1
--怎么发了这么多帖子。
--> 测试数据: [JIT-mucus]
create table [JIT-mucus] (id int,machinenum varchar(100))
insert into [JIT-mucus]
select 1,'1#;2#;5#;16#' union all
select 2,'3#;4#'
goselect id,substring(machinenum+';',b.number,charindex(';',machinenum+';',b.number+1)-b.number) X
from [JIT-mucus] a,master..spt_values b
where b.type='p' and b.number between 1 and 100 and substring(';'+machinenum,b.number,1)=';'