模仿group_concat（）与GROUP BY相结合 [英] Mimic group_concat() combined with GROUP BY

问题描述

我有一个像这样的预订表：

  booking_id，
日期，
客户端，
发起人
  

我试图获得每月总结：

 选择
 MONTH（日期）AS M，
赞助商，
客户，
 COUNT（booking_id ）AS c 
从预订
 GROUP BY 
 M，赞助商，客户
  

现在我想看看客户在哪个日期做了预订。我尝试使用STUFF（）（在本文中引用：模拟Microsoft SQL Server 2005中的group_concat MySQL函数？），但它与group-by语句冲突。

按照要求提供样本数据。目前我有以下几种：

  M赞助商客户c 
三月AB $ 3 
三月FE x 4 
 April AB x 2 
  

所需输出：

  M保荐人客户c日期
三月AB y 3 12,15,18 
三月FE x 4 16,19,20,21 
 4月AB x 2 4，8 
  

如果数字是日数（例如3月12日， 3月15日，3月18日）。在MySQL中，我会使用group_concat（日期）来获取最后一列。 > SELECT [Month] = DATENAME（MONTH，M），Sponsor，Client，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]> = xM AND b。[date]< DATEADD（MONTH，1，xM）
ORDER BY [date]
FOR XML PATH（''）），1，2，''）
FROM
（
SELECT
M = DATEADD（MONTH，DATEDIFF（MONTH，'19000101'，[日期]），'19000101'），
保荐人，
客户，
COUNT（booking_id）AS c
FROM dbo.booking
GROUP BY DATEADD（MONTH，DATEDIFF（MONTH，'19000101'，[date]），'19000101'），
赞助商，
客户
）AS x
M，赞助商，客户的订单;


请注意，如果赞助商/客户的组合在同一天有两笔预订，则日期编号将会出现在列表中两次。

编辑以下是我测试的结果：

 （
 booking_id INT IDENTITY（1,1）PRIMARY KEY，
 [date] DATE，
赞助商VARCHAR（32），
 Client VARCHAR（32）
）; 
 
 INSERT @booking（[日期]，赞助商，客户）VALUES 
（'20120312'，'AB'，'y'），（'20120315'，'AB'，'y '），（'20120318'，'AB'，'y'），
（'20120316'，'FE'，'x'），（'20120319'，'FE'，'x'），（ '20120321'，'FE'，'x'），（'20120320'，'FE'，'x'），
（'20120404'，'AB'，'x'），（'20120408'， 'AB'， 'X'）; 
 
 SELECT [Month] = DATENAME（MONTH，M），Sponsor，Client，c，
 [dates] = STUFF（（SELECT'，'+ RTRIM（DATEPART（DAY，[date ]））
 FROM @booking AS b 
 WHERE b.Sponsor = x.Sponsor 
 AND b.Client = x.Client 
 AND b。[date]> = xM AND b。[date]< DATEADD（MONTH，1，xM）
 ORDER BY [date] 
 FOR XML PATH（''）），1，2，''）
 FROM 
（
 SELECT 
 M = DATEADD（MONTH，DATEDIFF（MONTH，'19000101'，[日期]），'19000101'），
保荐人，
客户， 
 COUNT（booking_id）AS c 
 FROM @booking 
 GROUP BY DATEADD（MONTH，DATEDIFF（MONTH，'19000101'，[date]），'19000101'），
赞助商，
客户
）AS x 
 M，赞助商，客户的订单; 
  

结果：

 月份赞助商客户c日期
 ------- ------- ------- ------- -------- ------ 
三月AB y 3 12,15,18 
三月FE x 4 16,19,20,21 
四月AB x 2 4,8 
  

I have a table 'booking' like this:

booking_id,
date,
client,
sponsor

I'm trying to get a monthly summary:

SELECT 
  MONTH(date) AS M,
  Sponsor,
  Client,
  COUNT(booking_id) AS c
FROM booking
GROUP BY
 M, Sponsor, Client

Now I want to see at which dates the client made bookings. I tried using STUFF() (referenced in this post: Simulating group_concat MySQL function in Microsoft SQL Server 2005?) but it conflicts with the group-by statement.

Sample data as per request. Currently i have the following:

M       Sponsor     Client  c     
March   AB          y       3
March   FE          x       4
April   AB          x       2

Desired output:

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

Where the numbers are the day-numers (e.g. 12 march, 15 march, 18 march). In mysql I would use group_concat(date) to get the last column.

Big kudos for the answer :-)

解决方案

SELECT [Month] = DATENAME(MONTH, M), Sponsor, Client, 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('')), 1, 2, '')
FROM 
(
  SELECT 
      M = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', [date]), '19000101'),
      Sponsor,
      Client,
      COUNT(booking_id) AS c
    FROM dbo.booking
    GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, '19000101', [date]), '19000101'),
      Sponsor,
      Client
) AS x
ORDER BY M, Sponsor, Client;

Note that if a combination of sponsor/client has two bookings on the same day, the day number will appear in the list twice.

EDIT Here is how I tested:

DECLARE @booking TABLE
( 
  booking_id INT IDENTITY(1,1) PRIMARY KEY,
  [date] DATE,
  Sponsor VARCHAR(32),
  Client VARCHAR(32)
);

INSERT @booking([date], Sponsor, Client) VALUES
('20120312','AB','y'), ('20120315','AB','y'), ('20120318','AB','y'),
('20120316','FE','x'), ('20120319','FE','x'), ('20120321','FE','x'), ('20120320','FE','x'),
('20120404','AB','x'), ('20120408','AB','x');

SELECT [Month] = DATENAME(MONTH, M), Sponsor, Client, c,
  [dates] = STUFF((SELECT ', ' + RTRIM(DATEPART(DAY, [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, 1, x.M) 
    ORDER BY [date]
    FOR XML PATH('')), 1, 2, '')
FROM 
(
  SELECT 
      M = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', [date]), '19000101'),
      Sponsor,
      Client,
      COUNT(booking_id) AS c
    FROM @booking
    GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, '19000101', [date]), '19000101'), 
      Sponsor, 
      Client
) AS x
ORDER BY M, Sponsor, Client;

Results:

Month   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

这篇关于模仿group_concat（）与GROUP BY相结合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！