如何检查MySQL表中多列重复项？
要在MySQL中检查重复项，可以使用具有by子句的group。语法如下。
select yourColumnName1,yourColumnName2,......N,count(*) as anyVariableName from yourTableName group by yourColumnName1,yourColumnName2 having count(*) > 1;
为了理解上述语法，让我们创建一个表。创建表的查询如下。
create table DuplicateDemo -> ( -> StudentId int not null, -> StudentFirstName varchar(100), -> StudentLastName varchar(100), -> Primary Key(StudentId) -> );
使用insert命令在表中插入一些记录。查询如下。
insert into DuplicateDemo values(1,'John','Smith'); insert into DuplicateDemo values(2,'Mike','Jones'); insert into DuplicateDemo values(3,'David','Smith'); insert into DuplicateDemo values(4,'Carol','Taylor'); insert into DuplicateDemo values(5,'David','Smith'); insert into DuplicateDemo values(6,'John','Smith'); insert into DuplicateDemo values(7,'John','Taylor');
使用select语句显示表中的所有记录。
查询如下-
select *from DuplicateDemo;
以下是输出。
+-----------+------------------+-----------------+ | StudentId | StudentFirstName | StudentLastName | +-----------+------------------+-----------------+ | 1 | John | Smith | | 2 | Mike | Jones | | 3 | David | Smith | | 4 | Carol | Taylor | | 5 | David | Smith | | 6 | John | Smith | | 7 | John | Taylor | +-----------+------------------+-----------------+ 7 rows in set (0.00 sec)
这是用于检查表中重复项的查询。
select StudentFirstName,StudentLastName,count(*) as Total from DuplicateDemo -> group by StudentFirstName,StudentLastName -> having count(*) > 1;
以下是输出。
+------------------+-----------------+-------+ | StudentFirstName | StudentLastName | Total | +------------------+-----------------+-------+ | John | Smith | 2 | | David | Smith | 2 | +------------------+-----------------+-------+ 2 rows in set (0.00 sec)