mysql - 高效的短SQL查询

Question

这个问题已经有答案了:

奥 git _a (27 个回答)

已关闭 5 年前。

我有这张表称为论文

|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      |

我想要的结果是

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

我已经尝试过

$sql = "SELECT DISTINCT (MAX(Paper_Code) 
     FROM papers WHERE Subject_Code = (SELECT DISTINCT Subject_Code))";

我是sql新手，尝试了很多次但一直失败。

Answer 1

select a.* from yourtable a join (
select subject_code, max(id)id
from yourtable
group by subject_code) b 
on a.subject_code=b.subject_code and a.id=b.id