mysql - 如何获取 MySQL 中外表的最大日期并仅返回包含该数据的行而不混合记录？

Question

我有一些 MySQL 表，我需要从中加入并返回数据。返回数据必须仅显示 JOINed 表之一的一行，但 MySQL 混合了这些行。

我尝试了使用子查询和普通 JOIN 与 GROUP 的不同方法，但结果几乎保持不变。

表结构示例

suppliers
id name ...
1  ACME ...
2  EMCA ...
3  ORG  ...`

ratings
id supplier_id rating expiry_date report_file
1  1           5.0    2017-01-31  a.pdf
3  1           7.9    2019-06-30  c.pdf
4  2           5.0    2016-01-31  d.pdf
5  2           2.0    2018-11-30  g.pdf
6  245         9.5    2009-03-31  p.pdf

spends
id report_id supplier_id amount
1  1         1           150.00
2  1         2           100.00
3  1         245         200.00

以下是我尝试解决此问题并返回正确数据集的示例查询，但没有成功。

SELECT 
    reports.id, 
    suppliers.id AS supplier_id, 
    suppliers.name, 
    ... 
    spends.amount, 
    ...  
    ratings.rating, 
    ratings.report_file, 
    ratings.expiry_date 

FROM reports 
    INNER JOIN spends ON reports.id=spends.report_id 
    INNER JOIN suppliers ON spends.supplier_id=suppliers.id 
    LEFT JOIN (
        SELECT id, 
            level,  
            report_file,  
            supplier_id, 
            MAX(expiry_date) AS expiry_date
        FROM ratings 
        GROUP BY supplier_id
    ) ratings ON (ratings.supplier_id=suppliers.id
        AND ratings.expiry_date >= reports.period_start)
    ... 
WHERE reports.id = 1 
GROUP BY spends.id  
ORDER BY spends.amount DESC

另一个查询

SELECT 
    reports.id, 
    suppliers.id AS supplier_id, 
    suppliers.name, 
    ... 
    spends.amount, 
    ...  
    ratings.rating, 
    ratings.report_file, 
    MAX(ratings.expiry_date) AS expiry_date 

FROM reports 
    INNER JOIN spends ON reports.id=spends.report_id 
    INNER JOIN suppliers ON spends.supplier_id=suppliers.id 
    LEFT JOIN ratings ON (ratings.supplier_id=suppliers.id
        AND ratings.expiry_date >= reports.period_start)
    ... 
WHERE reports.id = 1 
GROUP BY spends.id  
ORDER BY spends.amount DESC

我预计结果是

id supplier_id name  amount rating report_file expiry_date
1  1            ACME  150.00 7.9    c.pdf       2019-06-30
1  2            EMCA  100.00 2.0    g.pf        2018-11-30
1  245          MACE  200.00 null   null```

However, the actual output is

```sql
id supplier_id name  amount rating report_file expiry_date
1  1            ACME  150.00 5.0    a.pdf       2019-06-30
1  2            EMCA  100.00 5.0    d.pf        2018-11-30
1  245          MACE  200.00 null   null

请任何人告诉我如何解决这个问题。

Answer 1

尝试这样的查询:

SELECT 
    reports.id, 
    suppliers.id AS supplier_id, 
    suppliers.name, 
    ... 
    spends.amount, 
    ...  
    r.rating, 
    r.report_file, 
    r.expiry_date 

FROM reports 
    INNER JOIN spends ON reports.id=spends.report_id 
    INNER JOIN suppliers ON spends.supplier_id=suppliers.id 

    LEFT JOIN ratings r ON r.supplier_id=suppliers.id 
            AND r.`expire_date` = (
                SELECT MAX(`expire_date`) FROM ratings WHERE supplier_id = supplier.id GROUP BY supplier_id)
                ) as maxdate
    ... 
WHERE reports.id = 1 
GROUP BY spends.id  
ORDER BY spends.amount DESC