declare @str varchar(8000) set @str='snap-db1,snap-db2' select replace(@str,',snap-db2','/2')
谢谢大家的回复,原问题是这样子的:USE tempdb godrop table tb CREATE TABLE tb ( id INT, value VARCHAR(10) ) INSERT INTO tb VALUES ( 1, 'snap-db1' ) INSERT INTO tb VALUES ( 1, 'snap-db2' ) INSERT INTO tb VALUES ( 2, 'aaa' ) INSERT INTO tb VALUES ( 2, 'bbb' ) INSERT INTO tb VALUES ( 2, 'ccc' ) go SELECT id,STUFF( ( SELECT distinct ',' + value FROM dbo.tb AS G2 WHERE G2.id = G1.id FOR XML PATH('') ), 1, 1, '') AS value FROM dbo.tb AS G1 GROUP BY id;我如何改造上面的SQL得到我想要的结果呢? 非常感谢.
declare @str varchar(8000) set @str='snap-db1,snap-db2' select @str=substring(@str,0,9)+'/'+right(@str,1) select @str
create table tt(id int PRIMARY key ,idx varchar(50)) insert into tt values(1,'a')select @@identity select * from tt select * from tc create trigger txy on tc for update as begin if inserted.id=2 begin update tc set name=2222 end
endupdate tc set id=2 , name=3 where id=2drop table tb CREATE TABLE tb ( id INT, value VARCHAR(10) ) INSERT INTO tb VALUES ( 1, 'snap-db1' ) INSERT INTO tb VALUES ( 1, 'snap-db2' ) INSERT INTO tb VALUES ( 2, 'aaa' ) INSERT INTO tb VALUES ( 2, 'bbb' ) INSERT INTO tb VALUES ( 2, 'ccc' ) go SELECT id,replace(STUFF( ( SELECT distinct ',' + value FROM dbo.tb AS G2 WHERE G2.id = G1.id FOR XML PATH('') ), 1, 1, ''),',snap-db','/') AS value FROM dbo.tb AS G1 GROUP BY id;/* id value ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 snap-db1/2 2 aaa,bbb,ccc(2 行受影响) */
to xiequan2: 你好,我更改下问题描述,可能会更清晰些: USE tempdb go drop table tb CREATE TABLE tb ( id INT, value VARCHAR(10) ) INSERT INTO tb VALUES ( 1, 'snap-db1' ) INSERT INTO tb VALUES ( 1, 'snap-db2' ) INSERT INTO tb VALUES ( 2, 'aaa1' ) INSERT INTO tb VALUES ( 2, 'aaa2' ) INSERT INTO tb VALUES ( 2, 'aaa3' ) go SELECT id,STUFF( ( SELECT distinct ',' + value FROM dbo.tb AS G2 WHERE G2.id = G1.id FOR XML PATH('') ), 1, 1, '') AS value FROM dbo.tb AS G1 GROUP BY id;我希望得到的结果是: id value 1 snap-db1/2 2 aaa1/2/3
drop table tb CREATE TABLE tb ( id INT, value VARCHAR(10) ) INSERT INTO tb VALUES ( 1, 'snap-db1' ) INSERT INTO tb VALUES ( 1, 'snap-db2' ) INSERT INTO tb VALUES ( 2, 'aaa1' ) INSERT INTO tb VALUES ( 2, 'aaa2' ) INSERT INTO tb VALUES ( 2, 'aaa3' ) go ;with f as (SELECT id,STUFF( ( SELECT distinct ',' + value FROM dbo.tb AS G2 WHERE G2.id = G1.id FOR XML PATH('') ), 1, 1, '') AS value FROM dbo.tb AS G1 GROUP BY id) select replace(value,','+SUBSTRING(value,1,PATINDEX('%[0-9]%',value)-1),'/') from f /*---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- snap-db1/2 aaa1/2/3(2 行受影响)*/
set @str='snap-db1,snap-db2'
select replace(@str,',snap-db','/')/*
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
snap-db1/2(所影响的行数为 1 行)*/
set @str='snap-db1,snap-db2'
select replace(@str,','+SUBSTRING(@STR,1,PATINDEX('%[0-9]%',@STR)-1),'/')
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
snap-db1/2(所影响的行数为 1 行)
set @str='snap-db1,snap-db2' select stuff(@str,8,8,'\')
/*----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
snap-db\b2(1 行受影响)*
set @str='snap-db1,snap-db2'
select substring(@str,0,charindex(',',@str))+'/'+substring(@str,len(@str),1)
set @str='snap-db1,snap-db2'
select substring(@str,0,charindex(',',@str))+'/'+substring(@str,len(@str),1)snap-db1/2(1 行受影响)
select @str='snap-db1,snap-db2'
select replace(@str,',snap-db','/')
set @str='snap-db1,snap-db2'
select replace(@str,',snap-db2','/2')
godrop table tb
CREATE TABLE tb ( id INT, value VARCHAR(10) )
INSERT INTO tb
VALUES ( 1, 'snap-db1' )
INSERT INTO tb
VALUES ( 1, 'snap-db2' )
INSERT INTO tb
VALUES ( 2, 'aaa' )
INSERT INTO tb
VALUES ( 2, 'bbb' )
INSERT INTO tb
VALUES ( 2, 'ccc' )
go
SELECT id,STUFF(
( SELECT distinct ',' + value FROM dbo.tb AS G2 WHERE G2.id = G1.id
FOR XML PATH('')
), 1, 1, '') AS value
FROM dbo.tb AS G1
GROUP BY id;我如何改造上面的SQL得到我想要的结果呢? 非常感谢.
declare @str varchar(8000)
set @str='snap-db1,snap-db2'
select @str=substring(@str,0,9)+'/'+right(@str,1)
select @str
create table tt(id int PRIMARY key ,idx varchar(50))
insert into tt values(1,'a')select @@identity
select * from tt
select * from tc
create trigger txy on tc
for update
as
begin
if inserted.id=2
begin
update tc set name=2222
end
endupdate tc set id=2 , name=3 where id=2drop table tb
CREATE TABLE tb ( id INT, value VARCHAR(10) )
INSERT INTO tb
VALUES ( 1, 'snap-db1' )
INSERT INTO tb
VALUES ( 1, 'snap-db2' )
INSERT INTO tb
VALUES ( 2, 'aaa' )
INSERT INTO tb
VALUES ( 2, 'bbb' )
INSERT INTO tb
VALUES ( 2, 'ccc' )
go
SELECT id,replace(STUFF(
( SELECT distinct ',' + value FROM dbo.tb AS G2 WHERE G2.id = G1.id
FOR XML PATH('')
), 1, 1, ''),',snap-db','/')
AS value
FROM dbo.tb AS G1
GROUP BY id;/*
id value
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 snap-db1/2
2 aaa,bbb,ccc(2 行受影响)
*/
你好,我更改下问题描述,可能会更清晰些:
USE tempdb
go
drop table tb
CREATE TABLE tb ( id INT, value VARCHAR(10) )
INSERT INTO tb
VALUES ( 1, 'snap-db1' )
INSERT INTO tb
VALUES ( 1, 'snap-db2' )
INSERT INTO tb
VALUES ( 2, 'aaa1' )
INSERT INTO tb
VALUES ( 2, 'aaa2' )
INSERT INTO tb
VALUES ( 2, 'aaa3' )
go
SELECT id,STUFF(
( SELECT distinct ',' + value FROM dbo.tb AS G2 WHERE G2.id = G1.id
FOR XML PATH('')
), 1, 1, '') AS value
FROM dbo.tb AS G1
GROUP BY id;我希望得到的结果是:
id value
1 snap-db1/2
2 aaa1/2/3
CREATE TABLE tb ( id INT, value VARCHAR(10) )
INSERT INTO tb
VALUES ( 1, 'snap-db1' )
INSERT INTO tb
VALUES ( 1, 'snap-db2' )
INSERT INTO tb
VALUES ( 2, 'aaa1' )
INSERT INTO tb
VALUES ( 2, 'aaa2' )
INSERT INTO tb
VALUES ( 2, 'aaa3' )
go ;with f as
(SELECT id,STUFF(
( SELECT distinct ',' + value FROM dbo.tb AS G2 WHERE G2.id = G1.id
FOR XML PATH('')
), 1, 1, '') AS value
FROM dbo.tb AS G1
GROUP BY id)
select replace(value,','+SUBSTRING(value,1,PATINDEX('%[0-9]%',value)-1),'/') from f
/*----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
snap-db1/2
aaa1/2/3(2 行受影响)*/