sql - 我怎样才能加入这3张 table

Question

我有3张 table :

Trip  Promotion  Promotion Cost.
  1 ---- M   1  --------- M

样本数据包括:

TripID    TripName    Date
XYZ123     Hawaii    09/06/09
YTU574     Japan     09/09/09
GHR752     US        11/07/09


PromotionID    TripID    Name
1              XYZ123    Poster
2              XYZ123    Brochure
3              GHR752    TV ad

CostID    PromotionID   Cost
  1           1         $50
  2           1         $100
  3           1         $120
  4           3         $2000
  5           2         $500

我正在尝试构建这样的查询:

TripID     Number of Promotions     Total Cost
XYZ123             2                  $770
GHR752             1                  $2000

我有的是这个:

SELECT
      Trip.TripID, Count(Trip.TripID) AS [Number Of Promotions], Sum(PromotionCost.Cost) AS SumOfCost
FROM
     Trip
     INNER JOIN
         (Promotion
          INNER JOIN
          PromotionCost ON Promotion.PromotionID = PromotionCost.PromotionID
         ) ON Trip.TripID = Promotion.TripID
GROUP BY
       Trip.TripID;

它给了我这样的东西:

TripID     Number of Promotions     Total Cost
XYZ123             4                  $770
GHR752             1                  $2000

我不确定为什么促销数量会像第一个(XYZ123)那样困惑。似乎以某种方式 JOIN 正在影响它，因为如果我使用它:

SELECT
      Trip.TripID, Count(Trip.TripID) AS [Number Of Promotions], 
FROM
      Trip
      INNER JOIN
      Promotion ON Trip.TripID = Promotion.TripID
GROUP BY
      Trip.TripID;

它为我提供了正确的促销数量，仅为 2。

Answer 1

您可以在子查询中将每次促销的成本相加。这样，每次促销只能获得一行，而 COUNT 用于计算每次旅行的促销次数。例如:

select 
    t.TripId
,   count(p.PromotionId) as [Number of Promotions]
,   sum(pc.PromotionCost) as [Total Cost]
from trip t
left join promotions p on p.TripId = t.TripId
left join (
    select 
        PromotionId
    ,   PromotionCost = sum(cost)
    from Promotions
    group by PromotionId
) pc on pc.PromotionId = p.PromotionId
group by t.TripId

如果 MS Access 不允许子查询，您可以将子查询存储在 View 中，然后加入。