如何使用MySQL在一个或多个列中查找具有确切值的行？
为此，您可以将GROUPBYHAVING与子查询一起使用。让我们首先创建一个表-
create table DemoTable1861
(
Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
Name varchar(20),
Marks int
);使用插入命令在表中插入一些记录-
insert into DemoTable1861(Name,Marks) values('John',45);
insert into DemoTable1861(Name,Marks) values('Chris',74);
insert into DemoTable1861(Name,Marks) values('David',89);
insert into DemoTable1861(Name,Marks) values('Chris',74);
insert into DemoTable1861(Name,Marks) values('John',49);使用select语句显示表中的所有记录-
select * from DemoTable1861;
这将产生以下输出-
+----+-------+-------+ | Id | Name | Marks | +----+-------+-------+ | 1 | John | 45 | | 2 | Chris | 74 | | 3 | David | 89 | | 4 | Chris | 74 | | 5 | John | 49 | +----+-------+-------+ 5 rows in set (0.00 sec)
这是在一列或多列中查找具有确切值的行的查询：
select Id,Name,Marks from DemoTable1861
where (Name,Marks) IN ( select Name,Marks from DemoTable1861
group by Name,Marks
having count(*) > 1);这将产生以下输出-
+----+-------+-------+ | Id | Name | Marks | +----+-------+-------+ | 2 | Chris | 74 | | 4 | Chris | 74 | +----+-------+-------+ 2 rows in set (0.00 sec)