加入更多表时，聚合函数是返回不正确的值

Question

我使用以下查询进行总销售。

SELECT SUM([B].[TotalSale])  
FROM [dbo].[BookingDetail] [BF] WITH (READPAST)
INNER JOIN [dbo].[Booking] [B] WITH (READPAST) ON [B].[BookingDetailID] = [BF].[ID]
WHERE [BF].[MarketID] = '2'

我想添加另一列以获得总销售。为此，我必须与另一个名为Airtraveler的桌子一起加入。

，但是一旦我向查询添加新表

SELECT 
SUM([B].[TotalSale])  ,
SUM(CASE WHEN [B].[TravelSectorID] = 3 AND [B].[BookingStatusID] IN (16, 20, 22, 23)  THEN COALESCE([B].[TotalSale], 0.0)
         WHEN ([B].[TravelSectorID] = 1 AND [B].[IsDomestic] = 1 AND CONVERT(varchar, [AT].[FareDetails].query('string(/AirFareInfo[1]/PT[1])')) = 'FlightAndHotel') THEN [AT].[TotalSale] 
         ELSE 0 END) AS [GrossSale]
FROM [dbo].[BookingDetail] [BF] WITH (READPAST)
INNER JOIN [dbo].[Booking] [B] WITH (READPAST) ON [B].[BookingDetailID] = [BF].[ID] 
LEFT OUTER JOIN  [dbo].[AirTraveler] [AT] WITH(READPAST) ON [B].[ID] = [AT].[BookingID]
WHERE [BF].[MarketID] = '2'

它给出了[TotalSale]的错误结果。汇总函数返回错误的值，因为每个预订ID可能有多个Airtraveler，这是正确的。我该怎么办来解决聚合功能问题？

我实际上被卡住了。

我正在使用SQL Server。

预先感谢。

Answer 1

未进行测试或其他任何内容，但是当您加入较低的级别表，该表使标头表进行仔细计数时，您可以在加入

之前对其进行预汇集

这可能缺少一些开放/关闭的括号和别名，但希望您可以解决

SELECT 
SUM([B].[TotalSale])  ,
SUM(CASE WHEN [B].[TravelSectorID] = 3 
AND [B].[BookingStatusID] IN (16, 20, 22, 23)  
THEN COALESCE([B].[TotalSale], 0.0)
         WHEN ([B].[TravelSectorID] = 1 AND [B].[IsDomestic] = 1 
         THEN [AT].[TotalSale] 
         ELSE 0 END) AS [GrossSale]
FROM [dbo].[BookingDetail] [BF] WITH (READPAST)
INNER JOIN [dbo].[Booking] [B] WITH (READPAST) ON [B].[BookingDetailID] = [BF].[ID] 
LEFT OUTER JOIN  
(
SELECT BookingID,  SUM(CASE WHEN 
CONVERT(varchar(50), [FareDetails].query('string(/AirFareInfo[1]/PT[1])')) 
= 'FlightAndHotel') THEN [TotalSale] ELSE 0 END) TotalSale
FROM [dbo].[AirTraveler] [AT] WITH(READPAST) 
GROUP BY BookingID
) AT
ON [B].[ID] = [AT].[BookingID]
WHERE [BF].[MarketID] = '2'

我也给了您的Varchar铸件 - 我认为如果您不这样做，那将是1，所以您的案子永远都不为