时间戳

首页 > 解决方案 > SQL按最大列排序和内连接多个表

mysql - SQL按最大列排序和内连接多个表

问题描述

我有 2 张桌子

Papers

|ID | Paper_Code | Subject_Code |
| 0 | 2018/Eng/01|     ENG      |
| 1 | 2018/Eng/02|     ENG      |
| 2 | 2018/CS/01 |     CS       |
| 3 | 2018/Sci/01|     Sci      |
| 4 | 2018/Eng/03|     ENG      |

Subjects

|Subject_Code|Subject_Name|
|ENG         |English     |
|Sci         |Science     |
|CS          |Computers   |

我通过使用这个 SQL CODE 实现了下表，它只显示了每个不同 Subject_Code 的 Max Paper_Code，这正是我想要的。

$sql = "SELECT Paper_Code FROM papers
       WHERE(Subject_Code,Paper_Code)
       IN(SELECT Subject_Code,MAX(Paper_Code)
       FROM Papers GROUP BY Subject_Code)
        ";

|ID | Paper_Code | Subject_Code |
| 4 | 2018/Eng/03|     ENG      |
| 2 | 2018/CS/01 |     CS       |
| 3 | 2018/Sci/01|     Sci      |

Now I want to INNER JOIN The Subjects Table to give me results like this

|ID | Paper_Code | Subject_Code | Subject_Name |
| 4 | 2018/Eng/03|     ENG      | English      |
| 2 | 2018/CS/01 |     CS       | Computers    |
| 3 | 2018/Sci/01|     Sci      | Science      |

我试过这个但失败了

$sql = "SELECT Paper_Code FROM Papers
       WHERE(Subject_Code,Paper_Code)
       IN(SELECT Subject_Code,MAX(Paper_Code)
       FROM Papers GROUP BY Subject_Code)
       INNER JOIN Subjects
       ON Papers.Subject_Code = Subjects.Subject_Code";

标签： mysqlsql

解决方案

JOIN之前WHERE：

编辑：正如 Barmar 的评论所说，限定所有列：

SELECT p.Paper_Code
FROM Papers p
INNER JOIN Subjects s
    ON p.Subject_Code = s.Subject_Code
WHERE (p.Subject_Code, p.Paper_Code)
   IN (SELECT p2.Subject_Code, MAX(p2.Paper_Code)
       FROM Papers p2
       GROUP BY p2.Subject_Code)

推荐阅读