34,590
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE #tb1
(
id INT,
NAME NVARCHAR(50)
)
INSERT INTO #tb1 ( id, NAME )VALUES ( 1, N'张一')
INSERT INTO #tb1 ( id, NAME )VALUES ( 2, N'张二')
INSERT INTO #tb1 ( id, NAME )VALUES ( 3, N'张三')
INSERT INTO #tb1 ( id, NAME )VALUES ( 4, N'张四')
SELECT NAME FROM #tb1
/*
结果:
张一
张二
张三
张四
*/
/*
我想要的结果是↓
张一/张二/张三/张四
寻求这个sql的写法。越简单越好!^^
*/
--DROP TABLE #tb1
if object_id('tb1') is not null drop table tb1
CREATE TABLE tb1
(
id INT,
NAME NVARCHAR(50)
)
INSERT INTO tb1 ( id, NAME )VALUES ( 1, N'张一')
INSERT INTO tb1 ( id, NAME )VALUES ( 2, N'张二')
INSERT INTO tb1 ( id, NAME )VALUES ( 3, N'张三')
INSERT INTO tb1 ( id, NAME )VALUES ( 4, N'张四')
--1函数的方法
create function dbo.FC_Str()
returns varchar(1000)
as
begin
declare @str varchar(1000)
set @str=''
select @str=@str+'/'+Name from tb1
return stuff(@str,1,1,'')
end
select distinct dbo.FC_Str() from tb1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
张一/张二/张三/张四
(1 行受影响)
--2字符串
declare @sql varchar(100)
set @sql=''
select @sql=@sql+'/'+Name from tb1
select stuff(@sql,1,1,'')
----------------------------------------------------------------------------------------------------
张一/张二/张三/张四
(1 行受影响)
if object_id('tb1') is not null drop table tb1
CREATE TABLE tb1
(
id INT,
NAME NVARCHAR(50)
)
INSERT INTO tb1 ( id, NAME )VALUES ( 1, N'张一')
INSERT INTO tb1 ( id, NAME )VALUES ( 2, N'张二')
INSERT INTO tb1 ( id, NAME )VALUES ( 3, N'张三')
INSERT INTO tb1 ( id, NAME )VALUES ( 4, N'张四')
create function dbo.FC_Str()
returns varchar(1000)
as
begin
declare @str varchar(1000)
set @str=''
select @str=@str+'/'+Name from tb1
return stuff(@str,1,1,'')
end
select distinct dbo.FC_Str() from tb1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
张一/张二/张三/张四
(1 行受影响)
CREATE TABLE #tb1
(
id INT,
NAME NVARCHAR(50)
)
INSERT INTO #tb1 ( id, NAME )VALUES ( 1, N'张一')
INSERT INTO #tb1 ( id, NAME )VALUES ( 2, N'张二')
INSERT INTO #tb1 ( id, NAME )VALUES ( 3, N'张三')
INSERT INTO #tb1 ( id, NAME )VALUES ( 4, N'张四')
select name = stuff((select '/'+name as [text()] from #tb1 for xml path('')),1,1,'')
/*
张一/张二/张三/张四
*/