在三张桌子上工作，我的分组不工作

Question

我必须列出在时间1和时间2之间的一行中不同的 电台售出的门票数量之间的差异。我有三张桌子：时间1，时间2和地点。

create table fares_jan18 (
    station varchar(100), 
    ff int 
); 

create table fares_feb1 (
    station varchar(100), 
    ff int 
); 

create table stations (
    name varchar(100), 
    line varchar(50) 
); 

我使用这个查询：

SELECT 
    fares_jan18.station AS name, 
    SUM(fares_feb1.ff - fares_jan18.ff) AS diff_feb1_jan18 
FROM 
    fares_jan18 
    JOIN fares_feb1 ON fares_jan18.station = fares_feb1.station 
    JOIN stations ON fares_jan18.station = stations.name 
WHERE 
    stations.line ="Broadway" 
GROUP BY 
    name 
ORDER BY 
    name; 

它确实给我正确的表，但是当曾经有不止一个记录，我得到的复制差的总和。例如，具有两个值的电台的结果应该是33254，并且“给我66508”。我已经从我的查询中删除了该组，但似乎是记录而不是两个。

我已经阅读了所有我可以在StackOverflow，但我无法让我的头在我做错了什么？谢谢！！

这里是我的表的图像：

Answer 1

你需要预先聚集每个单独或者你得到一个笛卡尔结果......由于查询是针对单个百老汇，一个记录将导致各站工作的每个日期的数据时加入来源

SELECT 
     justF18.name, 
     justFeb1.SumFF - justF18.SumFF as FF_Diff 
    from 
     (SELECT f18.station AS name, 
       SUM(f18.ff) AS Sumff 
      FROM 
       fares_jan18 f18 
       JOIN stations s ON f18.station = s.name 
       AND s.line = "Broadway" 
      group by 
       f18.station) justF18 
     JOIN 
     (SELECT feb1.station AS name, 
       SUM(feb1.ff) AS Sumff 
      FROM 
       fares_feb1 feb1 
       JOIN stations s ON feb1.station = s.name 
       AND s.line = "Broadway" 
      group by 
       feb1.station) justFeb1 
     ON justF18.name = justFeb1.name 
    order by 
     JustFeb18.name 

Answer 2

你在两个票价表创建都具有相同的站行的笛卡尔积，然后相加每一对的差异。您需要分别在子查询中计算每个表中的总计，然后减去它们。

SELECT s.name, feb1.total - jan18.total AS diff_feb1_jan18 
FROM stations AS s 
JOIN (SELECT station, SUM(ff) AS total 
     FROM fares_feb1 
     GROUP BY station) AS feb1 
    ON feb1.station = s.station 
JOIN (SELECT station, SUM(ff) AS total 
     FROM fares_jan18 
     GROUP BY station) AS jan18 
    ON jan18.station = s.station 
WHERE s.line = "Broadway" 
ORDER BY s.name