MySQL |最多选择两个来自一张表的值

Question

我有 ID(电视剧 ID)、T(译者 ID)、S(季)、E(剧集)

我需要转换这个:

| ID | T | S | E |  
| 1  | 1 | 1 | 2 |  
| 1  | 1 | 1 | 3 |  
| 1  | 2 | 1 | 3 |  
| 2  | 3 | 2 | 1 |  
| 2  | 3 | 3 | 1 |  

进入此:

| ID | T | S | E |  
| 1  | 1 | 1 | 3 |  
| 1  | 2 | 1 | 3 |  
| 2  | 3 | 3 | 1 |  

我的想法:

SELECT `ID, `T`, `S`, `E`
FROM `UPDATE`
WHERE
`S` = (SELECT MAX(`S`) FROM `UPDATE` WHERE `ID` = `ID`)
AND `E` = (SELECT MAX(`E`) FROM `UPDATE` WHERE `ID` = `ID` AND `S` = (SELECT MAX(`S`) FROM `UPDATE` WHERE `ID` = `ID`))
GROUP BY `ID`, `T`
ORDER BY `TIMESTAMP` DESC

Answer 1

我认为这会起作用。您需要首先确定每个 ID 和 T 的最大 S。确定这些后，您可以在所有三个字段上连接回原始表以获得最大 E。

   SELECT A.ID, A.T, A.S, MAX(B.E) as E
    FROM (
    SELECT ID, T, MAX(S) AS S
    FROM `UPDATE` U
    GROUP BY ID, T) A
    INNER JOIN `UPDATE` B
    ON A.ID = B.ID AND A.T = B.T AND A.S = B.S
    GROUP BY A.ID, A.T, A.S