要了解多列的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)