mysql 多个值连成串_SQL语句中把多条记录同一字段的值连接成一个字符串显示结果集(sql stuff) | 学步园...

SELECT STUFF('abcdef', 2, 3, 'ijklmn');

结果：aijklmndf。

1.

if object_id('[tb]') is not null drop table [tb]

go

create table [tb]([Inv] int,[order] varchar(4))

insert [tb]

select 10001,'P111' union all

select 10001,'P112' union all

select 10001,'P113' union all

select 10002,'P114' union all

select 10002,'P115'

--------------开始查询--------------------------

select inv, [order]=stuff((select ','+[order] from tb t where Inv=tb.Inv for xml path('')), 1, 1, '')

from tb

group by Inv

select id1, [id3]=stuff((select ','+id3 from id_t t where id1=id_t.id1 for xml path('')), 1, 1, '')

from id_t GROUP BY id1

2.

001

00101

20

001

00101

80

001

00101

75

002

00101

20

002

00101

10

003

00101

70

004

00101

70

004

00101

70

004

00101

70

id1

id2

id3

if object_id('f_str') is not null drop function f_str

go

CREATE FUNCTION dbo.f_str(@id int)

RETURNS varchar(8000)

AS

BEGIN

DECLARE @r varchar(8000)

SET @r = ''

SELECT @r = @r + ';' + id3

FROM id_t

WHERE id1=@id

RETURN STUFF(@r, 1, 1, '')

END

GO

-- 调用函数

SELECt id1, [order]=dbo.f_str(id1)

FROM id_t

GROUP BY id1