我有一张像这样的预订表：
booking_id,date,client,sponsor
我正在尝试获得每月摘要：
SELECT MONTH(date) AS M,Sponsor,Client,COUNT(booking_id) AS c FROM booking GROUP BY M,Client
现在我想知道客户在哪些日期预订.我尝试使用STUFF()(在这篇文章中引用：Simulating group_concat MySQL function in Microsoft SQL Server 2005?)但它与group-by语句冲突.
根据请求提供样本数据.目前我有以下内容：
M Sponsor Client c March AB y 3 March FE x 4 April AB x 2
期望的输出：
M Sponsor Client c dates March AB y 3 12,15,18 March FE x 4 16,19,20,21 April AB x 2 4,8
数字是天数(例如12月3日,3月15日,3月18日).在MysqL中我会使用group_concat(date)来获取最后一列.
答案的大赞誉:-)
解决方法
SELECT [Month] = DATENAME(MONTH,M),c,[dates] = STUFF((SELECT ',' + RTRIM(DATEPART(DAY,[date]))
FROM dbo.booking AS b
WHERE b.Sponsor = x.Sponsor
AND b.Client = x.Client
AND b.[date] >= x.M AND b.[date] < DATEADD(MONTH,1,x.M)
ORDER BY [date]
FOR XML PATH('')),2,'')
FROM
(
SELECT
M = DATEADD(MONTH,DATEDIFF(MONTH,'19000101',[date]),'19000101'),COUNT(booking_id) AS c
FROM dbo.booking
GROUP BY DATEADD(MONTH,Client
) AS x
ORDER BY M,Client;
请注意,如果赞助商/客户的组合在同一天有两次预订,则该日期号将在列表中出现两次.
编辑这是我测试的方式：
DECLARE @booking TABLE
(
booking_id INT IDENTITY(1,1) PRIMARY KEY,[date] DATE,Sponsor VARCHAR(32),Client VARCHAR(32)
);
INSERT @booking([date],Client) VALUES
('20120312','AB','y'),('20120315',('20120318',('20120316','FE','x'),('20120319',('20120321',('20120320',('20120404',('20120408','x');
SELECT [Month] = DATENAME(MONTH,[date]))
FROM @booking AS b
WHERE b.Sponsor = x.Sponsor
AND b.Client = x.Client
AND b.[date] >= x.M AND b.[date] < DATEADD(MONTH,COUNT(booking_id) AS c
FROM @booking
GROUP BY DATEADD(MONTH,Client;
结果：
Month Sponsor Client c dates ------- ------- ------- ------- -------------- March AB y 3 12,18 March FE x 4 16,21 April AB x 2 4,8
