A表结构:----------------------
ID Group Num
----------------------
111 A 1
----------------------
111 B 2
-----------------------
222 C 1怎样通过SQL Server 查询得到如下结果?查询后的结果:
----------------------
ID Group Num
----------------------
111 AB 3
-----------------------
222 C 1
ID Group Num
----------------------
111 A 1
----------------------
111 B 2
-----------------------
222 C 1怎样通过SQL Server 查询得到如下结果?查询后的结果:
----------------------
ID Group Num
----------------------
111 AB 3
-----------------------
222 C 1
Group=(select ''+Group from A where ID=t.ID for xml path('')) ,
Num=sum(Num)
from A t group by ID
Id ,
[group] = STUFF(( SELECT
'' + [group]
FROM
ta
WHERE
id = t.ID
FOR
XML PATH('')
), 1, 1, '') ,
SUM(num) num
FROM
ta t
GROUP BY
id
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO
CREATE TABLE [tb]([ID] INT,[Group] VARCHAR(1),[Num] INT)
INSERT [tb]
SELECT 111,'A',1 UNION ALL
SELECT 111,'B',2 UNION ALL
SELECT 222,'C',1
--------------开始查询--------------------------SELECT [ID],[Group]=(SELECT [Group]+'' FROM tb WHERE [ID]=t.[ID] FOR XML PATH('')),SUM([Num]) FROM [tb] AS t
GROUP BY [ID]
----------------结果----------------------------
/*
ID Group (无列名)
111 AB 3
222 C 1
*/