22,207
社区成员
发帖
与我相关
我的任务
分享
select *
from [book]
cross apply Split(reader,',') tbl_Ids
可以去看看这篇文章http://blog.csdn.net/dotnetstudio/article/details/22928977
USE [master]
GO
/****** Object: UserDefinedFunction [dbo].[Split] Script Date: 05/08/2014 14:45:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[Split]
(
@c VARCHAR(MAX) ,
@split VARCHAR(50)
)
RETURNS @t TABLE ( col VARCHAR(50) )
AS
BEGIN
WHILE ( CHARINDEX(@split, @c) <> 0 )
BEGIN
INSERT @t( col )
VALUES ( SUBSTRING(@c, 1, CHARINDEX(@split, @c) - 1) )
SET @c = STUFF(@c, 1, CHARINDEX(@split, @c), '')
END
INSERT @t( col ) VALUES ( @c )
RETURN
END
-------------------------------END OF FUNCTION----------------------------------------------
对你的表的建表语句
USE [master]
GO
/****** Object: Table [dbo].[book] Script Date: 05/08/2014 15:05:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[book](
[book_name] [nvarchar](50) NULL,
[reader] [nvarchar](50) NULL
) ON [PRIMARY]
GO
最后的查询语句
Select book_name,col From [book] A
OUTER APPLY (Select * From dbo.[Split]((Select [reader] From [book] Where [book_name] = A.[book_name]),',')) B
CREATE Function [dbo].[f_split](@aString varchar(max),@pattern varchar(10))
returns @temp table(A varchar(max))
--实现split功能 的函数
--说明:@aString,字符串,如a:b:c;@pattern,分隔标志,如 :
as
begin
declare @i int
set @aString=rtrim(ltrim(@aString))
set @i=charindex(@pattern,@aString)
while @i>=1
begin
insert @temp values(left(@aString,@i-1))
set @aString=substring(@aString,@i+1,len(@aString)-@i)
set @i=charindex(@pattern,@aString)
end
if @aString<>''
insert @temp values(@aString)
return
end
GO
Select A.[书名] From [table] A OUTER APPLY (Select * From dbo.f_split((Select [作者] From [table] Where ID = A.ID),',')) B