SQL Server查询 - 在同一个表上计数两次

问题描述：

这个问题一直让我疯狂，虽然我很久没写过SQL了。SQL Server查询 - 在同一个表上计数两次

我有2个表：

CREATE TABLE [Entities].[Events](
[EventID] [int] IDENTITY(1,1) NOT NULL, 
[EventVenueID] [int] NULL, 
[EntityID] [int] NOT NULL, 
[OrganisationID] [int] NULL, 
[Title] [nvarchar](300) NOT NULL, 
[DateStart] [datetime] NOT NULL, 
[DateFinish] [datetime] NOT NULL, 
[Notes] [nvarchar](max) NULL, 
[MinimumProviders] [int] NOT NULL, 
[MinimumAttendees] [int] NOT NULL, 
[ShowCalendar] [bit] NOT NULL, 
[CreationDate] [datetime] NOT NULL, 
[IsEnabled] [bit] NOT NULL) 

CREATE TABLE [Entities].[EventParticipants](
[ParticipantID] [int] IDENTITY(1,1) NOT NULL, 
[EventID] [int] NOT NULL, 
[PersonID] [int] NOT NULL, 
[ParticipantType] [int] NOT NULL, 
[ParticipantStatus] [int] NOT NULL, 
[AttendanceStatus] [int] NULL)

和查询：

SELECT 
e.EventID, 
ev.VenueName, 
e.EntityID, 
o.Name AS 'Organisation', 
e.Title, 
e.DateStart, 
e.DateFinish, 
e.Notes, 
e.MinimumProviders, 
e.MinimumAttendees, 
e.CreationDate, 
COUNT(epp.ParticipantID) AS 'ProvidersConfirmed', 
COUNT(epa.ParticipantID) AS 'AttendeesConfirmed', 
e.IsEnabled 
    FROM 
Entities.Events e 
    LEFT OUTER JOIN 
Entities.EventVenues ev 
    ON 
e.EventVenueID = ev.EventVenueID 
    LEFT OUTER JOIN 
Entities.Organisations o 
    ON 
e.OrganisationID = o.OrganisationID 
    LEFT OUTER JOIN 
Entities.EventParticipants epp 
    ON 
e.EventID = epp.EventID AND epp.ParticipantType = 1 AND epp.ParticipantStatus = 3 
    LEFT OUTER JOIN 
Entities.EventParticipants epa 
    ON 
e.EventID = epa.EventID AND epa.ParticipantType = 2 AND epa.ParticipantStatus = 3 
    GROUP BY 
e.EventID, 
ev.VenueName, 
e.EntityID, 
o.Name, 
e.Title, 
e.DateStart, 
e.DateFinish, 
e.Notes, 
e.MinimumProviders, 
e.MinimumAttendees, 
e.CreationDate, 
e.IsEnabled

计数被产生奇怪的结果，例如在EventParticipants表我有以下数据：

参与者类型= 1（3记录）参与者类型= 2（1记录）参与者状态= 3（4记录）

的查询数应返回：

ProvidersConfirmed = 3 AttendeesConfirmed = 1

然而，它的返回

ProvidersConfirmed = 3 AttendeesConfirmed = 3

谁能帮助？

不知道我明白你想要什么，但你可以尝试计算不同的值。 'COUNT（distinct epp.ParticipantID）'和'COUNT（epa.ParticipantID）'。它可能会做你想做的事。 –

'COUNT（DISTINCT ...'会起作用，但它可能产生更好的查询计划，只能加入到'EventParticipants'一次 –

答

基本上所有的连接都抛弃了这个查询。当您从Event加入EventParticipants有一个事件记录和三个EventParticipant记录，这相当于在您的数据集中有三个重复的eventID。然后你再次加入EventParticipants。因为你要连接3行，所以你会得到3次重复的结果。

有办法修剪下来的结果，但它可能会更好只JOIN一次，并做一些选择性的数学在您的累计，是这样的：

SELECT 
e.EventID, 
ev.VenueName, 
e.EntityID, 
o.Name AS 'Organisation', 
e.Title, 
e.DateStart, 
e.DateFinish, 
e.Notes, 
e.MinimumProviders, 
e.MinimumAttendees, 
e.CreationDate, 
COUNT(CASE WHEN ep.ParticipantType = 1 THEN 1 ELSE NULL END) AS 'ProvidersConfirmed', 
COUNT(CASE WHEN ep.ParticipantType = 2 THEN 1 ELSE NULL END) AS 'AttendeesConfirmed', 
e.IsEnabled 
    FROM 
Entities.Events e 
    LEFT OUTER JOIN 
Entities.EventVenues ev 
    ON 
e.EventVenueID = ev.EventVenueID 
    LEFT OUTER JOIN 
Entities.Organisations o 
    ON 
e.OrganisationID = o.OrganisationID 
    LEFT OUTER JOIN 
Entities.EventParticipants epp 
    ON 
e.EventID = epp.EventID AND epp.ParticipantStatus = 3 
    GROUP BY 
e.EventID, 
ev.VenueName, 
e.EntityID, 
o.Name, 
e.Title, 
e.DateStart, 
e.DateFinish, 
e.Notes, 
e.MinimumProviders, 
e.MinimumAttendees, 
e.CreationDate, 
e.IsEnabled

和SQL的COUNT将扔掉所有的空值，所以它只会计算唯一的值。

谢谢，完美的作品 – Paul

答

考虑：

COUNT(DISTINCT epp.ParticipantID) AS 'ProvidersConfirmed', 
COUNT(DISTINCT epa.ParticipantID) AS 'AttendeesConfirmed'

近了，但是这仍然会算NULL值，因此....

COUNT(DISTINCT epp.ParticipantID) 
    - MAX(CASE WHEN epp.ParticipantID IS NULL THEN 1 ELSE 0 END) 
    AS 'ProvidersConfirmed', 
COUNT(DISTINCT epa.ParticipantID) 
    - MAX(CASE WHEN epa.ParticipantID IS NULL THEN 1 ELSE 0 END) 
    AS 'AttendeesConfirmed'

答

你确定你需要Left Outer Join

因为Left Outer Join将选择所有左表中的行。我想这就是问题，所有的行都是从事件参与者的一个点上选择的。

如果不需要使用Outer，您可能会想要使用INNER连接。

SQL Server查询 - 在同一个表上计数两次

相关推荐