SQL Server 中同一子查询的多个聚合

发布于 2024-12-02 15:12:02 字数 998 浏览 0 评论 0原文

有没有更好的方法来做到这一点，而无需重复的子查询，只选择不同的字段？

SELECT Name, er.DateEventStarts, e.LocationName,
(SELECT count(*) FROM Ticket t WHERE t.Deleted = 0 AND Refunded = 0 AND t.EventRepetitionID = er.EventRepetitionID) AS NoOfAttendees,
(SELECT sum(t.TicketTotalCost) FROM Ticket t WHERE t.Deleted = 0 AND Refunded = 0 AND t.EventRepetitionID = er.EventRepetitionID) AS NoOfAttendees,
(SELECT sum(t.OnlinePayFee) FROM Ticket t WHERE t.Deleted = 0 AND Refunded = 0 AND t.EventRepetitionID = er.EventRepetitionID) AS OnlinePayFee,
(SELECT sum(t.OnlinePayTotalCost) FROM Ticket t WHERE t.Deleted = 0 AND Refunded = 0 AND t.EventRepetitionID = er.EventRepetitionID) AS OnlinePayTotalCost  
FROM [Event] e
JOIN EventRepetition er ON er.EventRepetitionID = (SELECT TOP 1 EventRepetitionID FROM         EventRepetition er2 WHERE er2.EventID = e.EventID)

正如您所看到的，子查询几乎相同，但只是对不同的行进行求和或计数。我觉得必须有更好的方法来做到这一点，但不知道还能怎么做。我想这可能不是很有效。

如果这个问题已经得到解答，我深表歉意，但我只是想不出一种方法来描述这个问题，所以无法寻找解决方案。

原文

Is there a better way of doing this without having the repeated sub queries that just select a different field?

SELECT Name, er.DateEventStarts, e.LocationName,
(SELECT count(*) FROM Ticket t WHERE t.Deleted = 0 AND Refunded = 0 AND t.EventRepetitionID = er.EventRepetitionID) AS NoOfAttendees,
(SELECT sum(t.TicketTotalCost) FROM Ticket t WHERE t.Deleted = 0 AND Refunded = 0 AND t.EventRepetitionID = er.EventRepetitionID) AS NoOfAttendees,
(SELECT sum(t.OnlinePayFee) FROM Ticket t WHERE t.Deleted = 0 AND Refunded = 0 AND t.EventRepetitionID = er.EventRepetitionID) AS OnlinePayFee,
(SELECT sum(t.OnlinePayTotalCost) FROM Ticket t WHERE t.Deleted = 0 AND Refunded = 0 AND t.EventRepetitionID = er.EventRepetitionID) AS OnlinePayTotalCost  
FROM [Event] e
JOIN EventRepetition er ON er.EventRepetitionID = (SELECT TOP 1 EventRepetitionID FROM         EventRepetition er2 WHERE er2.EventID = e.EventID)

As you can see the sub queries are almost the same but just sum or count a different row. I feel there must be a better way of doing this but can't see how else to do it. I imagine this is probably not very efficient.

I apologise if this has already been answered but I just cannot think of a way to describe this problem so was unable to search for a solution.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

千年*琉璃梦 2024-12-09 15:12:02

这可能会起作用：

SELECT
    Name,
    er.DateEventStarts,
    e.LocationName,
t.NoOfAttendees,
t.TotalTickets,
t.OnlinePayFee,
t.OnlinePayTotalCost  
FROM [Event] e
JOIN EventRepetition er
    ON
        er.EventRepetitionID = (SELECT TOP 1 EventRepetitionID FROM EventRepetition er2 WHERE er2.EventID = e.EventID)
join
    (select EventRepetitionID,COUNT(*),SUM(TicketTotalCost),SUM(OnlinePayFee),SUM(OnlinePayTotalCost)
    from Ticket
    where Deleted = 0 and Refunded = 0
    group by EventRepetitionID) t (EventRepetitionID,NoOfAttendees,TotalTickets,OnlinePayFee,OnlinePayTotalCost)
        on
            er.EventRepetitionID = t.EventRepetitionID

这只应该扫描票证表一次。

This might do the trick:

SELECT
    Name,
    er.DateEventStarts,
    e.LocationName,
t.NoOfAttendees,
t.TotalTickets,
t.OnlinePayFee,
t.OnlinePayTotalCost  
FROM [Event] e
JOIN EventRepetition er
    ON
        er.EventRepetitionID = (SELECT TOP 1 EventRepetitionID FROM EventRepetition er2 WHERE er2.EventID = e.EventID)
join
    (select EventRepetitionID,COUNT(*),SUM(TicketTotalCost),SUM(OnlinePayFee),SUM(OnlinePayTotalCost)
    from Ticket
    where Deleted = 0 and Refunded = 0
    group by EventRepetitionID) t (EventRepetitionID,NoOfAttendees,TotalTickets,OnlinePayFee,OnlinePayTotalCost)
        on
            er.EventRepetitionID = t.EventRepetitionID

This should only scan the ticket table once.

回复收藏 0 原文

念﹏祤嫣 2024-12-09 15:12:02

没有测试下一个，但它会是这样的：

select Q.name, Q.ateEventStarts, Q.locationName,
        count(*), sum(T.TicketTotalCost), sum(T.TicketTotalCost), 
        sum(T.OnlinePayFee), sum(T.OnlinePayTotalCost)
    from ( 
        select Name, er.DateEventStarts, e.LocationName, er.EventRepetitionID
            from Event e JOIN EventRepetition er on er.EventRepetitionID = 
                (select top 1 EventRepetitionID from         
                    EventRepetition er2 where er2.EventID = e.EventID) ) Q
    left outer join Ticket T
        on t.Deleted = 0 and T.Refunded = 0 
            and t.EventRepetitionID = Q.EventRepetitionID

Not tested the next one, but it will be something like this:

select Q.name, Q.ateEventStarts, Q.locationName,
        count(*), sum(T.TicketTotalCost), sum(T.TicketTotalCost), 
        sum(T.OnlinePayFee), sum(T.OnlinePayTotalCost)
    from ( 
        select Name, er.DateEventStarts, e.LocationName, er.EventRepetitionID
            from Event e JOIN EventRepetition er on er.EventRepetitionID = 
                (select top 1 EventRepetitionID from         
                    EventRepetition er2 where er2.EventID = e.EventID) ) Q
    left outer join Ticket T
        on t.Deleted = 0 and T.Refunded = 0 
            and t.EventRepetitionID = Q.EventRepetitionID

回复收藏 0 原文

~没有更多了~