MySQL 选择给定日期之前的最新日期来计算 SUM(#)

Question

我正在为我的一门课做作业，并四处寻找问题的答案，但找不到。我正在使用两个表。

他们是:

TaxAreaAuthority                TaxRates
+--------------+---------+      +--------------+------------+-------------+
| TaxAuthority | TaxArea |      | TaxAuthority | Effective  | AuthTaxRate |
+--------------+---------+      +--------------+------------+-------------+
| city1        | city1   |      | city1        | 1993-01-01 |         1.0 |
| city2        | city2   |      | city1        | 1994-01-01 |         1.5 |
| city3        | city3   |      | city2        | 1993-09-01 |         1.5 |
| county1      | city1   |      | city2        | 1994-01-01 |         2.0 |
| county1      | city2   |      | city2        | 1995-01-01 |         2.5 |
| county2      | city3   |      | city3        | 1993-01-01 |         1.9 |
| state1       | city1   |      | city3        | 1993-07-01 |         2.3 |
| state1       | city2   |      | county1      | 1993-01-01 |         2.3 |
| state1       | city3   |      | county1      | 1994-10-01 |         2.5 |
+--------------+---------+      | county1      | 1995-01-01 |         2.7 |
                                | county2      | 1993-01-01 |         2.4 |
                                | county2      | 1994-01-01 |         2.7 |
                                | county2      | 1995-01-01 |         2.8 |
                                | state1       | 1993-01-01 |         0.5 |
                                | state1       | 1994-01-01 |         0.8 |
                                | state1       | 1994-07-01 |         0.9 |
                                | state1       | 1994-10-01 |         1.1 |
                                +--------------+------------+-------------+

这是带有示例的主要问题:

What is the Tax Rate for city2 on Nov 1, 1994?

city2 = 2.0, county1 = 2.5, state1 = 1.1

Total = 5.6

我可以使用以下语句从 TaxAreaAuthority 选择县和州:

SELECT TaxAuthority FROM TaxAreaAuthority
    WHERE TaxAuthority LIKE 'county%'
    AND TaxArea='city2';

SELECT TaxAuthority FROM TaxAreaAuthority
    WHERE TaxAuthority LIKE 'state%'
    AND TaxArea='city2';

这就是我被困住的地方。我用于选择正确日期的代码已关闭，这导致它无法将正确的 AuthTaxRate 项目添加在一起。

SELECT SUM(AuthTaxRate) as 'Tax_Total' FROM TaxRates
    WHERE EXTRACT(YEAR FROM Effective)='1994'
        AND TaxAuthority=(
            (SELECT TaxAuthority FROM TaxRates
                WHERE TaxAuthority='city2'
                AND EXTRACT(year FROM Effective)='1994')
            OR
            (SELECT TaxAuthority FROM TaxAreaAuthority
                WHERE TaxAuthority LIKE 'county%'
                AND TaxArea='city2')
            OR
            (SELECT TaxAuthority FROM TaxAreaAuthority
                WHERE TaxAuthority LIKE 'state%'
                AND TaxArea='city2')
            )
    ;

它返回:

+-----------+
| Tax_Total |
+-----------+
|      11.5 |
+-----------+

但我需要它来匹配他上面给出的示例。

任何帮助将不胜感激。我很确定这都是因为我没有选择正确的日期来确定要使用哪个 TaxAuthority 和 AuthTaxRate。再次感谢您的所有帮助。

Answer 1

您想要对分组最大值求和。

首先，通过连接表并对结果进行分组来找到相关的(TaxAuthority,Effective)对:

SELECT   TaxAuthority, MAX(Effective) Effective
FROM     TaxRates JOIN TaxAreaAuthority USING (TaxAuthority)
WHERE    TaxArea = 'city2' AND Effective <= '1994-11-01'
GROUP BY TaxAuthority

然后将结果连接回 TaxRates 表:

SELECT SUM(AuthTaxRate)
FROM   TaxRates NATURAL JOIN (
  SELECT   TaxAuthority, MAX(Effective) Effective
  FROM     TaxRates JOIN TaxAreaAuthority USING (TaxAuthority)
  WHERE    TaxArea = 'city2' AND Effective <= '1994-11-01'
  GROUP BY TaxAuthority
) t