CREATE
TABLE Test
(
ID INT,
Name VARCHAR(20),
Times DateTime,
Salary DECIMAL
)
INSERT INTO Test
VALUES
(1, '小A', '2009-01-01',100),
(1, '小A', '2009-02-01',200),
(1, '小A', '2009-03-01',300),
(2, '小B', '2009-04-01',400),
(2, '小B', '2009-05-01',500),
(2, '小B', '2009-06-01',600)
CREATE FUNCTION GetString4Test(@ID INT)
RETURNS VARCHAR(2000)
AS
BEGIN
DECLARE @ STR VARCHAR(2000)
SET @ STR= ''
SELECT @ STR=@ STR+ CONVERT( VARCHAR(20),Times,120)+ ',' FROM Test WHERE ID=@ID
RETURN @ STR
END
GO
SELECT DISTINCT Name,dbo.GetString4Test(ID), SUM(Salary) FROM Test
GROUP BY ID, Name
(
ID INT,
Name VARCHAR(20),
Times DateTime,
Salary DECIMAL
)
INSERT INTO Test
VALUES
(1, '小A', '2009-01-01',100),
(1, '小A', '2009-02-01',200),
(1, '小A', '2009-03-01',300),
(2, '小B', '2009-04-01',400),
(2, '小B', '2009-05-01',500),
(2, '小B', '2009-06-01',600)
CREATE FUNCTION GetString4Test(@ID INT)
RETURNS VARCHAR(2000)
AS
BEGIN
DECLARE @ STR VARCHAR(2000)
SET @ STR= ''
SELECT @ STR=@ STR+ CONVERT( VARCHAR(20),Times,120)+ ',' FROM Test WHERE ID=@ID
RETURN @ STR
END
GO
SELECT DISTINCT Name,dbo.GetString4Test(ID), SUM(Salary) FROM Test
GROUP BY ID, Name
比游标性能更好一些,写法更方便。
但是注意在连接查询求聚合结果时,会有一些注意的地方。
适用于记录数较多,或者禁止使用游标函数的场景。
结果如下:
小B 2009-04-01 00:00:00,2009-05-01 00:00:00,2009-06-01 00:00:00, 1500
转载于:https://blog.51cto.com/handongliu/145955