时间戳

首页 > 解决方案 > SQL Server - 使用 GROUP BY 子句的 SUM 和逗号分隔值

sql - SQL Server - 使用 GROUP BY 子句的 SUM 和逗号分隔值

问题描述

我有 2 张桌子：

ND事件：

EventId  EndTime       
33       2020-10-23 15:00:00.000
33       2020-10-23 15:00:00.000
35       2020-10-21 03:30:00.000
35       2020-10-24 15:00:00.000
35       2020-10-25 15:00:00.000
34       2020-10-23 15:00:00.000

活动预约：

Id DocId  EventId Amount
1  7647    34     10.00
2  7647    34     10.00
3  28531   33     20.00
4  7647    35     20.00
5  7647    35     100.00
6  7647    35     200.00

我希望结果是这样的：

DocId  EventId  Amount  Id
7647    34      20.00   1,2
28531   33      20.00   3
7647    35      320.00  4,5,6

我尝试过的是：

select e.Amount,e.DoctorId,e.EventId,
    Id= STUFF(    
     (SELECT DISTINCT  ',' + CAST(e.Id as nvarchar(max))   
     from NDEvent nd 
     inner join EventAppointment e on nd.Id = e.EventId
     where 
     GETDATE() > nd.EndTime
     GROUP BY 
     e.Amount,e.DoctorId,e.EventId,e.Id
      FOR XML PATH(''))    
        , 1, 1, ''    
    ) 
    from NDEvent nd 
    inner join EventAppointment e on nd.Id = e.EventId
    where 
    GETDATE() > nd.EndTime
    GROUP BY 
    e.Amount,e.DoctorId,e.EventId

但它没有给出预期的结果。

任何人都可以帮助这个查询吗？或者指出我正确的方向？谢谢你。

标签： sqlsql-serveraggregate-functions

解决方案

看起来您根本不需要在NDEvent此处列出（尽管我将其包含在示例数据中）。正义SUM与STRING_AGG反对EventAppointment：

USE Sandbox
GO

WITH NDEvent AS(
    SELECT *
    FROM (VALUES(33,CONVERT(datetime,'2020-10-23T15:00:00.000')),
                (33,CONVERT(datetime,'2020-10-23T15:00:00.000')),
                (35,CONVERT(datetime,'2020-10-21T03:30:00.000')),
                (35,CONVERT(datetime,'2020-10-24T15:00:00.000')),
                (35,CONVERT(datetime,'2020-10-25T15:00:00.000')),
                (34,CONVERT(datetime,'2020-10-23T15:00:00.000')))V(EventID,EndTime)),
EventAppointment AS(
    SELECT *
    FROM (VALUES(1,7647 ,34,10.00),
                (2,7647 ,34,10.00),
                (3,28531,33,20.00),
                (4,7647 ,35,20.00),
                (5,7647 ,35,100.00),
                (6,7647 ,35,200.00))V(Id,DocId, EventID, Amount))
SELECT DocID,
       EventID,
       SUM(Amount) AS Amount,
       STRING_AGG(Id,',') WITHIN GROUP (ORDER BY Id) AS IDs
FROM EventAppointment EA
GROUP BY DocId,
         EventID;

推荐阅读