???关于表中列的聚合的解决
发布时间：2020-12-13 19:47:17 所属栏目：百科 来源：网络整理
导读：SQLcode问题描述： 无论是在sql2000，还是在sql2005中,都没有提供字符串的聚合函数， 所以，当我们在处理下列要求时，会比较麻烦： 有表tb,如下： idvalue ----------- 1aa 1bb 2aaa 2bbb 2ccc 需要得到结果： id values ----------------- 1aa,bb 2aaa,bbb,
SQLcode问题描述：
无论是在sql2000，还是在sql2005中,都没有提供字符串的聚合函数，
所以，当我们在处理下列要求时，会比较麻烦：
有表tb,如下：
idvalue
-----------
1aa
1bb
2aaa
2bbb
2ccc
需要得到结果：
id
values
-----------------
1aa,bb
2aaa,bbb,ccc
即，
group
by
id,求value的和（字符串相加）
1.旧的解决方法
--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,
''
)
END
GO
--调用函数
SELECt
id,
values
=dbo.f_str(id)
FROM
tb
GROUP
BY
id
--2.新的解决方法
--示例数据
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
@tN
WHERE
id=A.id
FOR
XMLAUTO
),
'<Nvalue="'
,
','
),
'"/>'
,
''
),
''
)
)N
/*
--结果
id
values
---------------------------
1aa,bb
2aaa,ccc
(2行受影响)
--*/
--各种字符串分函数
--3.3.1使用游标法进行字符串合并处理的示例。
--处理的数据
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,
''
))
SELECT
@s=
','
+
CAST
(@col2
as
varchar
),@col1_old=@col1
END
FETCH
tb
INTO
@col1,@col2
END
INSERT
@t
VALUES
(@col1_old,
''
))
CLOSE
tb
DEALLOCATE
tb
--显示结果并删除测试数据
SELECT
*
FROM
@t
DROP
TABLE
tb
/*
--结果
col1col2
---------------------
a1,2
b1,2,3
--*/
GO
/*==============================================*/
--3.3.2使用用户定义函数，配合SELECT处理完成字符串合并处理的示例
--处理的数据
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,
''
))
END
GO
--调用函数
SELECT
col1,col2=dbo.f_str(col1)
FROM
tb
GROUP
BY
col1
--删除测试
DROP
TABLE
tb
DROP
FUNCTION
f_str
/*
--结果
col1col2
---------------------
a1,2
b1,3
--*/
GO
/*==============================================*/
--3.3.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
--合并处理
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
/*
--更新处理后的临时表
col1col2
-----------------------
a1
a1,2
b1
b1,2
b1,3
--*/
--得到最终结果
SELECT
col1,col2=
MAX
(col2)
FROM
#t
GROUP
BY
col1
/*
--结果
col1col2
---------------------
a1,2
b1,3
--*/
--删除测试
DROP
TABLE
tb,#t
GO
/*==============================================*/
--3.3.4.1每组<=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
'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
/*
--结果
col1col2
--------------------
a1,2
b1,2
c3
--*/
--3.3.4.2每组<=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
tba
GROUP
BY
col1
DROP
TABLE
tb
/*
--结果
col1col2
----------------------
a1,2
b1,3
c3
--*/
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,3'
union
all
select
2,N
'3,4'
Go
create
function
F_str(@cnamenvarchar(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
idcname
---------------------------------------------------------------------------------------------------------------
1张三,李四,王五
2王五,蔡六
（编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |