mysql - 每个类别每个 ID 的最高状态

Question

我正在努力解决以下问题:

我有一个名为 Article_Statuses 的表(以及主表 Articles，其中 ArticleID 作为 p.key)并且具有以下结构

ID, ArticleID, Status, Status_Date, Category

在这个表中我收集了每个类别(预定义)的文章(ArticleID)的所有状态，每个类别的最高ID是该类别的最新状态，这里有一些数据:

1, BB0001, LFS, 15-01-2015, LIC
2, BB0001, LFA, 19-01-2015, LIC
3, BB0001, SA, 10-01-2015, FIS
4, BB0001, CA, 19-01-2015, FIS
5, BB0002, LFS, 10-01-2015, LIC
6, BB0002, LFA, 11-01-2015, LIC
7, BB0003, CA, 19-01-2015, FIS

我想查询以下结果:

ArticleID, Status LIC, Status_Date LIC, Status FIS, Status_Date FIS
BB0001, LFA, 19-01-2015, CA, 19-01-2015
BB0002, LFA, 11-01-2015, ,
BB0003, , , CA, 19-01-2015

我发现以下解决方案仅适用于一个类别，我坚持添加其他类别...

SELECT `a`.`ArticleID`, `b`.`Status_Date` AS `LIC_Date`, `b`.`Status` AS `LIC_Status`
FROM `Articles` `a` 
    INNER JOIN `Article_Statuses` `b` ON `a`.`ArticleID` = `b`.`ArticleID`
    INNER JOIN ( SELECT `ArticleID`, MAX( `ID` ) `MAXID`
    FROM `Article_Statuses`
    WHERE `Category` = 'LIC' GROUP BY `ArticleID` ) `c`
    ON `b`.`ArticleID` = `c`.`ArticleID` AND `b`.`ID` = `c`.`MAXID`
WHERE `a`.`Partner` = 10
GROUP BY `a`.`ArticleID` 
ORDER BY `a`.`ArticleID` ASC 

Answer 1

您查询中的“合作伙伴”是什么意思？您之前没有在任何地方提到它，所以我猜它并不重要。

你有多少种不同的类别？只有两个或更多？我问是因为以这种方式返回数据远非快速和最佳。

它会是这样的:

SELECT 
  a.ArticleID, 
  b.Status_Date AS LIC_Date, b.Status AS LIC_Status,
  d.Status_Date AS FIS_Date, d.Status AS FIS_Status
FROM Articles AS a 
    INNER JOIN Article_Statuses AS b ON a.ArticleID = b.ArticleID
    INNER JOIN ( 
        SELECT ArticleID, MAX( ID ) AS ID
        FROM Article_Statuses
        WHERE Category = 'LIC' GROUP BY ArticleID
    ) AS c ON b.ArticleID = c.ArticleID AND b.ID = c.ID

    INNER JOIN Article_Statuses AS d ON a.ArticleID = d.ArticleID
    INNER JOIN ( 
        SELECT ArticleID, MAX( ID ) AS ID
        FROM Article_Statuses
        WHERE Category = 'FIS' GROUP BY ArticleID
    ) AS e ON d.ArticleID = e.ArticleID AND d.ID = e.ID

WHERE a.Partner = 10
ORDER BY a.ArticleID ASC 

基本上，您只是使用不同的别名重复连接 - 我使用了“d”和“e”，但最好使用有意义的东西，例如“LIC_category”，而不仅仅是“b”。

如果某些类别可以像您的示例中那样为空，则您还应该使用left join 而不是 inner join。