具有多个联接的MS Access查询返回重复的行

debugcn 发表于 Dev

凯尔·乔根森（Kyle Jorgensen）

我正在使用Access查询其中包含乘客信息的多个表。我已经能够将其归结为我认为是导致该问题的原因，但是我不确定如何解决它。

基本上有三个表：

旅客人数

ID   | PASSENGER_NAME | COST_CENTER
-------------------------------------
1    | John Wright    | 31231       
2    | Cheryl Brown   | 54555       
3    | Adam Yang      | 65655

FARE_LEVEL

ID   | TICKET_NUMBER  | PASSENGER_NAME | TICKET_AMT | IS_REFUND
---------------------------------------------------------------
1    | 14325435       | John Wright    | $632.64    | 0    
2    | 46746745       | Adam Yang      | $797.32    | 0  
3    | 45354434       | Cheryl Brown   | $2331.00   | 0  
4    | 67876456       | Cheryl Brown   | $990.11    | 0     
5    | 34654546       | Adam Yang      | $552.71    | 0  
6    | 14325435       | John Wright    | -$632.64   | 1    
7    | 87989879       | John Wright    | $123.11    | 0  
8    | 99124324       | Adam Yang      | $1114.42   | 0  
9    | 77231235       | Adam Yang      | $6232.32   | 0

INCENTIVE_LOG

ID   | PASSENGER_NAME | INCENTIVE_AMT
-------------------------------------
1    | Adam Yang      | $1000.00       
2    | Cheryl Brown   | $1000.00       
3    | John Wright    | $1000.00   
4    | John Wright    | $1000.00       
5    | John Wright    | $1000.00

我正在使用此查询将它们加入：

SELECT
PASSENGERS.PASSENGER_NAME,
Sum(FARE_LEVEL.TICKET_AMT) AS SumOfFARES,
Count(INCENTIVE_LOG.INCENTIVE) AS CountOfINCENTIVE,
Sum(INCENTIVE_LOG.INCENTIVE) AS SumOfINCENTIVE
FROM (PASSENGERS LEFT OUTER JOIN FARE_LEVEL ON PASSENGERS.PASSENGER_NAME = FARE_LEVEL.PASSENGER_NAME)
INNER JOIN INCENTIVE_LOG ON PASSENGERS.PASSENGER_NAME = INCENTIVE_LOG.PASSENGER_NAME
GROUP BY PASSENGERS.PASSENGER_NAME;

我要查找的结果是PASSENGER表中每位乘客的所有票价之和，以及PASSENGER表中每位乘客采取的所有奖励措施的总和。因此，对于“约翰·赖特”，我希望看到

PASSENGER_NAME | SumOfFares | CountOfIncentive | SumOfIncentive
---------------------------------------------------------------
John Wright    | $123.11    | 3                | $3000.00

相反，我得到的结果似乎是重复计算的。当我将GROUP BY更改为

GROUP BY PASSENGERS.PASSENGER_NAME, INCENTIVE_LOG.ID, FARE_LEVEL.ID;

我可以看到所有重复的行都加了错误。似乎正在为对应于乘客的FARE_LEVEL和INCENTIVE行的每种组合创建一行，因此，如果某人有7种票价和3种激励措施，则将创建21行。

我相当确定问题出在我的联接中，但是我不确定如何解决它。

编辑：我能够通过在原始查询中创建两个子查询来解决此问题

SELECT
    PASSENGERS.PASSENGER_NAME,
    Sum(Query4.SumOfTICKET_AMT) AS SumOfFARES,
    Sum(Query2.SumOfINCENTIVE) AS Incentive
FROM (PASSENGERS 
    LEFT OUTER JOIN (
        SELECT PASSENGERS.PASSENGER_NAME, Sum(FARE_LEVEL.TICKET_AMT) AS SumOfTICKET_AMT
        FROM PASSENGERS LEFT OUTER JOIN FARE_LEVEL ON PASSENGERS.PASSENGER_NAME = FARE_LEVEL.PASSENGER_NAME
        GROUP BY PASSENGERS.PASSENGER_NAME 
    ) AS Query4
    ON PASSENGERS.PASSENGER_NAME = Query4.PASSENGER_NAME)
    INNER JOIN (
        SELECT PASSENGERS.PASSENGER_NAME, Sum(INCENTIVE_LOG.INCENTIVE) AS SumOfINCENTIVE
        FROM PASSENGERS LEFT OUTER JOIN INCENTIVE_LOG ON PASSENGERS.PASSENGER_NAME = INCENTIVE_LOG.PASSENGER_NAME
        GROUP BY PASSENGERS.PASSENGER_NAME
    ) AS Query2
    ON PASSENGERS.PASSENGER_NAME = Query2.PASSENGER_NAME
GROUP BY PASSENGERS.PASSENGER_NAME;

马特

您必须首先对激励进行总结：

SELECT PASSENGERS.PASSENGER_NAME,
     Count(INCENTIVE_LOG.INCENTIVE) AS CountOfINCENTIVE,
     Sum(INCENTIVE_LOG.INCENTIVE) AS SumOfINCENTIVE
FROM (select PASSENGERS.PASSENGER_NAME, 
           Sum(FARE_LEVEL.TICKET_AMT) AS SumOfFARES
      From PASSENGERS 
           LEFT OUTER JOIN FARE_LEVEL 
                ON PASSENGERS.PASSENGER_NAME = FARE_LEVEL.PASSENGER_NAME
      Group by PASSENGERS.PASSENGER_NAME
      ) x
     INNER JOIN INCENTIVE_LOG 
          ON x.PASSENGER_NAME = INCENTIVE_LOG.PASSENGER_NAME
GROUP BY PASSENGERS.PASSENGER_NAME;

另外，您可以只创建第一个查询，该查询仅计算总激励，然后在此摘要中使用该查询。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。