重新的標題：如何在MySQL中根據點讚數和時間相關性對表格進行排序？

Question

我有一個簡單的問題，有兩個mysql表，一個儲存照片，另一個儲存讚。結構如下：

表 `photos` - id, name, image_id
表 `likes` - id_photos, timestamp

現在按照按讚數對照片進行排序並取得前3名很簡單，可以使用以下查詢語句：

SELECT photos.*
    , COUNT(likes.id) AS likes_count 
 FROM photos 
 LEFT JOIN likes ON photos.id = likes.id_photos 
 GROUP BY photos.id 
 ORDER BY likes_count DESC LIMIT 3;

但是我想要添加時間相關性，也就是說，新的點讚有更高的“權重”，即使某些照片有更多的點贊，但它們較舊，所以它們的排序低於按讚較少但較新的照片。

是否可能只使用MySQL解決這個問題？還是需要在PHP中進行額外的處理？

Answer 1

考慮一個情況，即 likes。 timestamp 是 UNIXTIME 字段，因此如果我們使用 SUM(timestamp) 而不是計算新的 likes 數量，將會給予更多的權重：

SELECT photos.*
    , COUNT(likes.id) AS likes_count 
    , SUM(likes.timestamp) AS likes_weight 
 FROM photos 
 LEFT JOIN likes ON photos.id = likes.id_photos 
 GROUP BY photos.id 
 ORDER BY likes_weight DESC LIMIT 3;

可以使用一些係數（例如 UNIX_TIMESTAMP()）

SELECT photos.*
    , COUNT(likes.id) AS likes_count 
    , SUM(likes.timestamp)/UNIX_TIMESTAMP() AS likes_weight 
 FROM photos 
 LEFT JOIN likes ON photos.id = likes.id_photos 
 GROUP BY photos.id 
 ORDER BY likes_weight DESC LIMIT 3;