SQLite 从 2 个连接中获得 MAX

Question

我想从连接到第一张 table ( Wine )的两张 table (品尝、评分)中获得一些分数。 1 种 Wine 可以拥有多次品尝和评级。

结构:

table wines
-----------
id 
name

table tastings
-------------
id 
wine_id
date
score

table ratings
-------------
id
wine_id
date
score

场景:1 款 Wine 有 2 次不同日期和分数的品尝，以及 3 次不同日期和分数的评级。

我想获取该酒的上次品尝(MAX日期)和分数，以及上次评级(MAX日期)和分数。

示例数据:

Wines:
-----
id: 1
name: Beaulieu Vineyard

tastings:
--------
id: 1
wine_id: 1
date: 2014-01-01
score: 4
-
id: 2
wine_id: 1
date: 2015-02-02
score: 5

ratings:
--------
id: 1
wine_id: 1
date: 2013-04-04
score: 6
-
id: 2
wine_id: 1
date: 2014-05-05
score: 7
-
id: 3
wine_id: 1
date: 2015-06-06
score: 8

所以想要的结果是:

Beaulieu Vineyard | Tasting: 2015-02-02 - score: 5 | Rating: 2015-06-06 - score: 8

我当前有效的查询:

SELECT   
wines.winery,  
MAX(tastings.date) AS tasting_date,
MAX(ratings.date) AS rating_date
FROM wines    
LEFT JOIN tastings ON wines.id = tastings.wine_id AND tastings.status=1  
LEFT JOIN ratings ON wines.id = ratings.wine_id AND ratings.status=1  
WHERE wines.status = 1   
GROUP BY wines.id    
ORDER BY wines.winery

问题是当我试图获得与每次品尝和每次配对相关的正确分数时:

SELECT   
wines.winery,  
MAX(tastings.date) AS tasting_date, tastings.score,
MAX(ratings.date) AS rating_date, ratings.score
FROM wines    
LEFT JOIN tastings ON wines.id = tastings.wine_id AND tastings.status=1  
LEFT JOIN ratings ON wines.id = ratings.wine_id AND ratings.status=1  
WHERE wines.status = 1   
GROUP BY wines.id    
ORDER BY wines.winery

分数错误:结果未显示与 MAX(日期)对应的正确分数。

Answer 1

SQLite 可以从与 MAX() 匹配的行中获取值，但这仅在存在单个 MAX() 时有效。

您必须使用子查询来查找值:

SELECT winery,
       (SELECT MAX(date)
        FROM tastings
        WHERE wine_id = wines.id
          AND status = 1
       ) AS tasting_date,
       (SELECT score
        FROM tastings
        WHERE wine_id = wines.id
          AND status = 1
        ORDER BY date DESC
        LIMIT 1
       ) AS tasting_score,
       (SELECT MAX(date)
        FROM ratings
        WHERE wine_id = wines.id
          AND status = 1
       ) AS rating_date,
       (SELECT score
        FROM ratings
        WHERE wine_id = wines.id
          AND status = 1
        ORDER BY date DESC
        LIMIT 1
       ) AS rating_score,
FROM wines
WHERE status = 1
ORDER BY winery

或者，在进行分组之前查找最大日期:

SELECT wines.winery,
       tasting_date,
       tastings.score,
       rating_date,
       ratings.score
FROM wines
LEFT JOIN (SELECT wine_id AS id,
                  MAX(date) AS tasting_date,
                  score
           FROM tastings
           WHERE status = 1
           GROUP BY wine_id
          ) AS tastings USING (id)
LEFT JOIN (SELECT wine_id AS id,
                  MAX(date) AS rating_date,
                  score
           FROM ratings
           WHERE status = 1
           GROUP BY wine_id
          ) AS ratings USING (id)
WHERE wines.status = 1
ORDER BY wines.winery