日期:2014-05-17 浏览次数:20499 次
ID mbno Msg ArriveTime
3674 8613581997450 测试内容ttt1 2012-08-15 12:26:35.000
3675 8613581997450 测试内容ttt2 2012-08-15 12:26:38.000
3676 8613581997451 测试内容ttt3 2012-08-15 12:26:46.000
3677 8613581997451 测试内容ttt4 2012-08-15 12:26:48.000
3678 8613581997451 测试内容ttt5 2012-08-15 12:26:49.000
ID mbno Msg ArriveTime
3674 8613581997450 测试内容ttt1,测试内容ttt2 2012-08-15 12:26:35.000
3676 8613581997451 测试内容ttt3,测试内容ttt4,测试内容ttt5 2012-08-15 12:26:46.000
CREATE TABLE t1
(
id INT,
mbno BIGINT,
msg VARCHAR(20),
arrivetime DATETIME
)
INSERT INTO t1
SELECT 3674,8613581997450,'测试内容ttt1','2012-08-15 12:26:35.000' UNION ALL
SELECT 3675,8613581997450,'测试内容ttt2','2012-08-15 12:26:38.000' UNION ALL
SELECT 3676,8613581997451,'测试内容ttt3','2012-08-15 12:26:46.000' UNION ALL
SELECT 3677,8613581997451,'测试内容ttt4','2012-08-15 12:26:48.000' UNION ALL
SELECT 3678,8613581997451,'测试内容ttt5','2012-08-15 12:26:49.000'
SELECT * FROM t1
;WITH aaa AS
(
SELECT ROW_NUMBER() OVER(PARTITION BY mbno ORDER BY id) AS rowindex,* FROM t1
)
SELECT a1.id,a1.mbno,b1.msg,a1.arrivetime FROM aaa AS a1 INNER JOIN
(
SELECT mbno,STUFF((SELECT ','+msg FROM t1 AS b WITH(NOLOCK) WHERE b.mbno=a.mbno FOR XML PATH('')),1,1,'') AS msg
FROM t1 AS a GROUP BY mbno
) AS b1 ON a1.mbno=b1.mbno
AND a1.rowindex=1
-----------------------------------
id mbno msg arrivetime
3674 8613581997450 测试内容ttt1,测试内容ttt2 2012-08-15 12:26:35.000
3676 8613581997451 测试内容ttt3,测试内容ttt4,测试内容ttt5 2012-08-15 12:26:46.000