无论是在sql 2000,还是在 sql 2005 中,都没有提供字符串的聚合函数,
所以,当我们在处理下列要求时,会比较麻烦:
有表tb, 如下:
id value
1 aa
1 bb
2 aaa
2 bbb
2 ccc
需要得到结果:
id
values
1 aa,bb
2 aaa,bbb,ccc
即,
group
by
id, 求 value 的和(字符串相加)
1. 旧的解决方法
CREATE
FUNCTION
dbo.f_str(@id
int
)
RETURNS
varchar
(8000)
AS
BEGIN
DECLARE
@r
varchar
(8000)
SET
@r =
''
SELECT
@r = @r +
','
+ value
FROM
tb
WHERE
id=@id
RETURN
STUFF(@r, 1, 1,
''
)
END
GO
SELECt
id,
values
=dbo.f_str(id)
FROM
tb
GROUP
BY
id
DECLARE
@t
TABLE
(id
int
, value
varchar
(10))
INSERT
@t
SELECT
1,
'aa'
UNION
ALL
SELECT
1,
'bb'
UNION
ALL
SELECT
2,
'aaa'
UNION
ALL
SELECT
2,
'bbb'
UNION
ALL
SELECT
2,
'ccc'
SELECT
*
FROM
(
SELECT
DISTINCT
id
FROM
@t
)A
OUTER
APPLY(
SELECT
[
values
]= STUFF(
REPLACE
(
REPLACE
(
(
SELECT
value
FROM
@t N
WHERE
id = A.id
FOR
XML AUTO
),
'<N value="'
,
','
),
'"/>'
,
''
), 1, 1,
''
)
)N
/*
id
values
1 aa,bb
2 aaa,bbb,ccc
(2 行受影响)
CREATE
TABLE
tb(col1
varchar
(10),col2
int
)
INSERT
tb
SELECT
'a'
,1
UNION
ALL
SELECT
'a'
,2
UNION
ALL
SELECT
'b'
,1
UNION
ALL
SELECT
'b'
,2
UNION
ALL
SELECT
'b'
,3
DECLARE
@t
TABLE
(col1
varchar
(10),col2
varchar
(100))
DECLARE
tb
CURSOR
LOCAL
FOR
SELECT
col1,col2
FROM
tb
ORDER
BY
col1,col2
DECLARE
@col1_old
varchar
(10),@col1
varchar
(10),@col2
int
,@s
varchar
(100)
OPEN
tb
FETCH
tb
INTO
@col1,@col2
SELECT
@col1_old=@col1,@s=
''
WHILE @@FETCH_STATUS=0
BEGIN
IF @col1=@col1_old
SELECT
@s=@s+
','
+
CAST
(@col2
as
varchar
)
ELSE
BEGIN
INSERT
@t
VALUES
(@col1_old,STUFF(@s,1,1,
''
))
SELECT
@s=
','
+
CAST
(@col2
as
varchar
),@col1_old=@col1
END
FETCH
tb
INTO
@col1,@col2
END
INSERT
@t
VALUES
(@col1_old,STUFF(@s,1,1,
''
))
CLOSE
tb
DEALLOCATE
tb
SELECT
*
FROM
@t
DROP
TABLE
tb
/*
col1 col2
a 1,2
b 1,2,3
GO
/*==============================================*/
CREATE
TABLE
tb(col1
varchar
(10),col2
int
)
INSERT
tb
SELECT
'a'
,1
UNION
ALL
SELECT
'a'
,2
UNION
ALL
SELECT
'b'
,1
UNION
ALL
SELECT
'b'
,2
UNION
ALL
SELECT
'b'
,3
GO
CREATE
FUNCTION
dbo.f_str(@col1
varchar
(10))
RETURNS
varchar
(100)
AS
BEGIN
DECLARE
@re
varchar
(100)
SET
@re=
''
SELECT
@re=@re+
','
+
CAST
(col2
as
varchar
)
FROM
tb
WHERE
col1=@col1
RETURN
(STUFF(@re,1,1,
''
))
END
GO
SELECT
col1,col2=dbo.f_str(col1)
FROM
tb
GROUP
BY
col1
DROP
TABLE
tb
DROP
FUNCTION
f_str
/*
col1 col2
a 1,2
b 1,2,3
GO
/*==============================================*/
CREATE
TABLE
tb(col1
varchar
(10),col2
int
)
INSERT
tb
SELECT
'a'
,1
UNION
ALL
SELECT
'a'
,2
UNION
ALL
SELECT
'b'
,1
UNION
ALL
SELECT
'b'
,2
UNION
ALL
SELECT
'b'
,3
SELECT
col1,col2=
CAST
(col2
as
varchar
(100))
INTO
#t
FROM
tb
ORDER
BY
col1,col2
DECLARE
@col1
varchar
(10),@col2
varchar
(100)
UPDATE
#t
SET
@col2=
CASE
WHEN
@col1=col1
THEN
@col2+
','
+col2
ELSE
col2
END
,
@col1=col1,
col2=@col2
SELECT
*
FROM
#t
/*
col1 col2
a 1
a 1,2
b 1
b 1,2
b 1,2,3
SELECT
col1,col2=
MAX
(col2)
FROM
#t
GROUP
BY
col1
/*
col1 col2
a 1,2
b 1,2,3
DROP
TABLE
tb,#t
GO
/*==============================================*/
CREATE
TABLE
tb(col1
varchar
(10),col2
int
)
INSERT
tb
SELECT
'a'
,1
UNION
ALL
SELECT
'a'
,2
UNION
ALL
SELECT
'b'
,1
UNION
ALL
SELECT
'b'
,2
UNION
ALL
SELECT
'c'
,3
SELECT
col1,
col2=
CAST
(
MIN
(col2)
as
varchar
)
+
CASE
WHEN
COUNT
(*)=1
THEN
''
ELSE
','
+
CAST
(
MAX
(col2)
as
varchar
)
END
FROM
tb
GROUP
BY
col1
DROP
TABLE
tb
/*
col1 col2
a 1,2
b 1,2
c 3
CREATE
TABLE
tb(col1
varchar
(10),col2
int
)
INSERT
tb
SELECT
'a'
,1
UNION
ALL
SELECT
'a'
,2
UNION
ALL
SELECT
'b'
,1
UNION
ALL
SELECT
'b'
,2
UNION
ALL
SELECT
'b'
,3
UNION
ALL
SELECT
'c'
,3
SELECT
col1,
col2=
CAST
(
MIN
(col2)
as
varchar
)
+
CASE
WHEN
COUNT
(*)=3
THEN
','
+
CAST
((
SELECT
col2
FROM
tb
WHERE
col1=a.col1
AND
col2
NOT
IN
(
MAX
(a.col2),
MIN
(a.col2)))
as
varchar
)
ELSE
''
END
+
CASE
WHEN
COUNT
(*)>=2
THEN
','
+
CAST
(
MAX
(col2)
as
varchar
)
ELSE
''
END
FROM
tb a
GROUP
BY
col1
DROP
TABLE
tb
/*
col1 col2
a 1,2
b 1,2,3
c 3
GO
if
not
object_id(
'A'
)
is
null
drop
table
A
Go
Create
table
A([id]
int
,[cname] nvarchar(2))
Insert
A
select
1,N
'张三'
union
all
select
2,N
'李四'
union
all
select
3,N
'王五'
union
all
select
4,N
'蔡六'
Go
if
not
object_id(
'B'
)
is
null
drop
table
B
Go
Create
table
B([id]
int
,[cname] nvarchar(5))
Insert
B
select
1,N
'1,2,3'
union
all
select
2,N
'3,4'
Go
create
function
F_str(@cname nvarchar(100))
returns
nvarchar(100)
as
begin
select
@cname=
replace
(@cname,ID,[cname])
from
A
where
patindex(
'%,'
+rtrim(ID)+
',%'
,
','
+@cname+
','
)>0
return
@cname
end
go
select
[id],dbo.F_str([cname])[cname]
from
B
id cname
1 张三,李四,王五
2 王五,蔡六