MySQL Select语句DISTINCT对于多列？

要了解多列的MySQL select语句DISTINCT，让我们看一个示例并创建一个表。创建表的查询如下

mysql> create table selectDistinctDemo
   -> (
   -> InstructorId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   -> StudentId int,
   -> TechnicalSubject varchar(100)
   -> );

使用insert命令在表中插入一些记录。查询如下

mysql> insert into selectDistinctDemo(StudentId,TechnicalSubject) values(121,'Java');
mysql> insert into selectDistinctDemo(StudentId,TechnicalSubject) values(121,'MongoDB');
mysql> insert into selectDistinctDemo(StudentId,TechnicalSubject) values(121,'MySQL');
mysql> insert into selectDistinctDemo(StudentId,TechnicalSubject) values(298,'Python');
mysql> insert into selectDistinctDemo(StudentId,TechnicalSubject) values(298,'SQL Server');
mysql> insert into selectDistinctDemo(StudentId,TechnicalSubject) values(397,'C#');

使用select语句显示表中的所有记录。查询如下

mysql> select *from selectDistinctDemo;

以下是输出

+--------------+-----------+------------------+
| InstructorId | StudentId | TechnicalSubject |
+--------------+-----------+------------------+
| 1            | 121       | Java             |
| 2            | 121       | MongoDB          |
| 3            | 121       | MySQL            |
| 4            | 298       | Python           |
| 5            | 298       | SQL Server       |
| 6            | 397       | C#               |
+--------------+-----------+------------------+
6 rows in set (0.00 sec)

这是对多列使用选择语句DISTINCT的查询

mysql> select InstructorId,StudentId,TechnicalSubject from selectDistinctDemo
-> where InstructorId IN
   -> (
   -> select max(InstructorId) from selectDistinctDemo
   -> group by StudentId
   -> )
-> order by InstructorId desc;

以下是输出

+--------------+-----------+------------------+
| InstructorId | StudentId | TechnicalSubject |
+--------------+-----------+------------------+
| 6            | 397       | C#               |
| 5            | 298       | SQL Server       |
| 3            | 121       | MySQL            |
+--------------+-----------+------------------+
3 rows in set (0.10 sec)