奈寻味导航网 » 文章资讯 » 根据一列或两列选择所有重复的 MySQL 行？

根据一列或两列选择所有重复的 MySQL 行？

2023-06-16 17:16:02 360

为此，请使用子查询和HAVING子句。让我们首先创建一个表-

mysql> create table DemoTable
   (
   StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   StudentFirstName varchar(20),
   StudentLastName varchar(20)
   );

使用插入命令在表中插入一些记录-

mysql> insert into DemoTable(StudentFirstName,StudentLastName) values('John','Smith');

mysql> insert into DemoTable(StudentFirstName,StudentLastName) values('Carol','Taylor');

mysql> insert into DemoTable(StudentFirstName,StudentLastName) values('John','Doe');

mysql> insert into DemoTable(StudentFirstName,StudentLastName) values('John','Brown');

mysql> insert into DemoTable(StudentFirstName,StudentLastName) values('David','Miller');

使用select语句显示表中的所有记录-

mysql> select *from DemoTable;

这将产生以下输出-

+-----------+------------------+-----------------+
| StudentId | StudentFirstName | StudentLastName |
+-----------+------------------+-----------------+
| 1         | John             | Smith           |
| 2         | Carol            | Taylor          |
| 3         | John             | Doe             |
| 4         | John             | Brown           |
| 5         | David            | Miller          |
+-----------+------------------+-----------------+
5 rows in set (0.00 sec)

以下是根据一列或两列选择所有重复行的查询。在这里，我们正在计算出现不止一次的名字，即重复-

mysql> select StudentId from DemoTable
   where StudentFirstName=(select StudentFirstName from DemoTable having count(StudentFirstName) > 1);

这将产生以下输出-

+-----------+
| StudentId |
+-----------+
| 1         |
| 3         |
| 4         |
+-----------+
3 rows in set (0.03 sec)

返回顶部
3162201930
czq8825@qq.com

根据一列或两列选择所有重复的 MySQL 行？

热门推荐

随机推荐