如何進行內連接和分組數據，同時將最近兩個月的相關條目限制為 1？

伊万話題

我想從一個表中獲取所有 URL 的列表，以及第二個表中每個 URL 的兩個評級。問題是一個評級應該來自當月（如果存在），另一個評級應該來自上個月（如果存在）。這可以用 MySQL 實現嗎？每個 URL 行的兩個評級都應分組到一個單元格中。

數據庫將每月填充一次或多次新值。

表結構和示例數據：

urls 
id | url
1  | google.com
2  | apple.com
3  | bing.com

ratings 
id | rating | url_id | created_at 
1  | 10      | 1      | 2018-10-10
2  | 8       | 2      | 2018-10-10
3  | 11      | 1      | 2018-11-10
4  | 5       | 2      | 2018-11-10
5  | 6       | 3      | 2018-11-25

最後，我的目標是顯示一個簡單的 URL 表並比較它們每個月的評分。

url        | previous rating | current rating
google.com | 10              | 11 (rating has increased)
apple.com  | 8               | 5 (rating has decreased)
bing.com   | -               | 6

這只是一個查詢的開始，我不知道如何限制每個 url 的 2 個評級，如上所述？

SELECT 
    urls.id, 
    urls.url, 
    ratings.rating

FROM urls

INNER JOIN  ratings 
ON urls.id = ratings.url_id

缺口

您可以使用條件聚合來獲得您想要的結果。我假設您希望使用每個月的平均評分進行比較。

SELECT u.url, 
       AVG(CASE WHEN MONTH(r.created_at) = MONTH(CURDATE() - INTERVAL 1 MONTH)
                 AND YEAR(r.created_at)  = YEAR(CURDATE() - INTERVAL 1 MONTH) THEN rating END) AS `previous rating`,
       AVG(CASE WHEN MONTH(r.created_at) = MONTH(CURDATE())
                 AND YEAR(r.created_at) = YEAR(CURDATE()) THEN rating END) AS `current rating`
FROM urls u
LEFT JOIN ratings r
ON r.url_id = u.id
GROUP BY u.url
ORDER BY `current rating` DESC

輸出：

url         previous rating     current rating
google.com  10.0000             11.0000
bing.com                        6.0000
apple.com   8.0000              5.0000

更新

只使用每個月的最後一個評級（如評論中所要求的）有點複雜。可以使用兩個JOINs 來完成包含前幾個月和當前月份的最後評級的表格：

SELECT u.url,
       pr.rating AS `previous rating`,
       cr.rating AS `current rating`
FROM urls u
LEFT JOIN (SELECT url_id, rating
           FROM ratings r1
           WHERE created_at = (SELECT MAX(created_at)
                               FROM ratings r2
                               WHERE r2.url_id = r1.url_id AND
                                   MONTH(created_at) = MONTH(CURDATE() - INTERVAL 1 MONTH) AND
                                   YEAR(created_at)  = YEAR(CURDATE() - INTERVAL 1 MONTH)
                               )
           ) pr ON pr.url_id = u.id
LEFT JOIN (SELECT url_id, rating
           FROM ratings r1
           WHERE created_at = (SELECT MAX(created_at)
                               FROM ratings r2
                               WHERE r2.url_id = r1.url_id
                               )
           ) cr ON cr.url_id = u.id
ORDER BY `current rating` DESC

更新了 dbfiddle 上的演示

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。