sql - MySql Select 语句根据关键字的主 ID 区分最新日期值

Question

基本上我在一个表中有一个 SEO 关键字列表，它们的排名包括在另一个表中输入的日期。

关键字

[keywordID(PKID)] [keyword]
----------------------------
1                 SEO
2                 Vancouver Homes

关键词排名

[rankingID(PKID)] [keywordID(FKID)] [ranking] [rankingDate(timestamp)]
----------------------------------------------------------------------
1                 1                 3         Jan 01, 2010
2                 1                 5         Jan 02, 2010
3                 1                 8         Jan 03,2010
4                 2                 4         Jan 01, 2010

这是我想要的结果。我想根据关键字的最新排名日期获得关键字的最新排名，且不重复。

对于上面的数据，它应该返回:

                  [keywordID] [rankingID] [ranking] [rankingDate] 
                  -----------------------------------------------
 SEO              1           3           8         Jan 03, 2010
 Vancouver Homes  4           2           4         Jan 01, 2010

为关键字选择最新排名的语句是什么？现在我让它返回关键字 ID 的重复行。

请询问是否需要更多信息这里是我当前的sql语句。

SELECT * 
  from keywords,
       keywordRankings 
 WHERE keywords.keywordID = keywordRankings.keywordID;

我目前适用的解决方案是...

SELECT s1.keywordID, keyword, rankingDate, ranking
FROM keywords s1, keywordRankings
WHERE keywordRankingRecord = (
SELECT MAX( s2.keywordRankingRecord )
FROM keywordRankings s2
WHERE s1.keywordID = s2.keywordID )     

Answer 1

解释了在一组中寻找最大值背后的基本思想 here .

SELECT k.keyword,
       r1.*
FROM   keyword AS k
       LEFT JOIN keywordRanking AS r1
         ON k.keywordid = r1.keywordid
       LEFT JOIN keywordRanking AS r2
         ON k.keywordid = r2.keywordid
            AND r1.rankingdate < r2.rankingdate
WHERE  r2.ranking IS NULL  

产量

+-----------------+-----------+-----------+---------+---------------------+
| keyword         | keywordID | rankingID | ranking | rankingDate         |
+-----------------+-----------+-----------+---------+---------------------+
| SEO             |         1 |         3 |       8 | 2010-01-03 00:00:00 | 
| Vancouver Homes |         2 |         4 |       4 | 2010-01-01 00:00:00 | 
+-----------------+-----------+-----------+---------+---------------------+