MySQL数据库查询数据，过滤重复数据保留一条数据

Z时代
2024-01-10
分类：综合

database

转自： http://www.maomao365.com/?p=10564 摘要:

下文讲述MySQL数据库查询重复数据时，只保留一条数据的方法
实现思路:
在MySQL数据库中没有row_number函数，所以我们需变向实现此功能。

--1.基础数据表查看
mysql>select*from `maomao365.com`;
+-------+------------+
| keyId | infoB |
+-------+------------+
|1| mysql test |
|129| sql |
|12913| sql |
|12913| sql |
|12913| sql |
+-------+------------+
5 rows inset (0.00 sec)
---2 群组编号
mysql>selectif(@keyId=a.keyId and@infoB=a.infoB,@r:=@r+1,@r:=1) as rowNumber,a.*,@keyId:=a.keyId,@infoB:=a.infoB
->from (select@keyId:=null,@infoB:=null,@r:=0) r, (select keyId,infoB from `maomao365.com` orderby keyId) a;
+-----------+-------+------------+-----------------+-----------------+
| rowNumber | keyId | infoB |@keyId:=a.keyId |@infoB:=a.infoB |
+-----------+-------+------------+-----------------+-----------------+
|1|1| mysql test |1| mysql test |
|1|129| sql |129| sql |
|1|12913| sql |12913| sql |
|2|12913| sql |12913| sql |
|3|12913| sql |12913| sql |
+-----------+-------+------------+-----------------+-----------------+
5 rows inset (0.00 sec)
---3 重复数据只显示一条
mysql> select*from (selectif(@keyId=a.keyId and@infoB=a.infoB,@r:=@r+1,@r:=1) as rowNumber,a.*,@keyId:=a.keyId,@infoB:=a.infoB
->from (select@keyId:=null,@infoB:=null,@r:=0) r, (select keyId,infoB from `maomao365.com` orderby keyId) a
-> ) as t where t.rowNumber =1;
+-----------+-------+------------+-----------------+-----------------+
| rowNumber | keyId | infoB |@keyId:=a.keyId |@infoB:=a.infoB |
+-----------+-------+------------+-----------------+-----------------+
|1|1| mysql test |1| mysql test |
|1|129| sql |129| sql |
|1|12913| sql |12913| sql |
+-----------+-------+------------+-----------------+-----------------+
3 rows inset (0.00 sec)