求sql语句。面试题。

[nitaiyoucala]

表结构和sql如下。。

CREATE TABLE [dbo].[tb1](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[f_id] [int] NULL,
	[number] [int] NULL,
	[type] [nvarchar](50) NULL,
 CONSTRAINT [PK_tb1] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET IDENTITY_INSERT [dbo].[tb1] ON 

GO
INSERT [dbo].[tb1] ([id], [f_id], [number], [type]) VALUES (1, 10, 2, N'上海')
GO
INSERT [dbo].[tb1] ([id], [f_id], [number], [type]) VALUES (2, 10, 1, N'北京')
GO
INSERT [dbo].[tb1] ([id], [f_id], [number], [type]) VALUES (3, 10, 1, N'天津')
GO
INSERT [dbo].[tb1] ([id], [f_id], [number], [type]) VALUES (4, 11, 2, N'上海')
GO
SET IDENTITY_INSERT [dbo].[tb1] OFF
GO

想得到结果如下:

[二月十六]

SELECT  f_id ,
        SUM(number) AS number ,
        STUFF(( SELECT  ',' + tb1.type
                FROM    tb1
                WHERE   f_id = a.f_id
                FOR
                XML PATH('')
                ), 1, 1, '') AS [type]
FROM    dbo.tb1 a
GROUP BY a.f_id 

[吉普赛的歌]

USE tempdb
GO
IF OBJECT_ID('tb1') IS NOT NULL DROP TABLE tb1
GO
CREATE TABLE [dbo].[tb1](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [f_id] [int] NULL,
    [number] [int] NULL,
    [type] [nvarchar](50) NULL,
CONSTRAINT [PK_tb1] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[tb1] ON 
INSERT [dbo].[tb1] ([id], [f_id], [number], [type]) VALUES (1, 10, 2, N'上海')
INSERT [dbo].[tb1] ([id], [f_id], [number], [type]) VALUES (2, 10, 1, N'北京')
INSERT [dbo].[tb1] ([id], [f_id], [number], [type]) VALUES (3, 10, 1, N'天津')
INSERT [dbo].[tb1] ([id], [f_id], [number], [type]) VALUES (4, 11, 2, N'上海')
SET IDENTITY_INSERT [dbo].[tb1] OFF
SELECT a.f_id
,SUM(a.number) AS number
,STUFF((SELECT ','+ b.[type] FROM dbo.tb1 AS b WHERE a.f_id=b.f_id FOR XML PATH('')),1,1,'')  AS [type] 
FROM tb1 AS a GROUP BY a.f_id
/*
f_id	number	type
10	4	上海,北京,天津
11	2	上海
*/

[xiaoxiangqing]

楼上的就可以

[学海无涯-回头是岸]

SELECT FID,SUM(*) AS "NUMBER",STUFF(SELECT ','+B.TYPE FROM tb1 B WHERE WHERE A.f_id=B.f_id FOR XML PATH(''),1,1,'') AS TYPE1 FROM tb1 A GROUP BY FID