我有一张像这样的预订表:
booking_id,date,client,sponsor@H_502_3@我正在尝试获得每月摘要:
SELECT MONTH(date) AS M,Sponsor,Client,COUNT(booking_id) AS c FROM booking GROUP BY M,Client@H_502_3@现在我想知道客户在哪些日期预订.我尝试使用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@H_502_3@期望的输出:
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@H_502_3@数字是天数(例如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;@H_502_3@请注意,如果赞助商/客户的组合在同一天有两次预订,则该日期号将在列表中出现两次.
编辑这是我测试的方式:
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;@H_502_3@结果:
Month Sponsor Client c dates ------- ------- ------- ------- -------------- March AB y 3 12,18 March FE x 4 16,21 April AB x 2 4,8@H_502_3@