我要评论摘要:
下文讲述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 in set (0.00 sec) ---2 群组编号 mysql> select if(@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` order by 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 in set (0.00 sec) ---3 重复数据只显示一条 mysql> select * from (select if(@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` order by 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 in set (0.00 sec)
定义临时变量 @keyid,@infob
每次查询时 都重新对 @keyid,@infob 赋值
在赋值前，都采用if对其进行判断，如果当前行记录信息和临时变量中的值相同时，则作为相同数据@r变量加1，否则@r变量变为1
采用以上模式，可以将重复的行记录进行编号
最后对重新编号后的记录，检索出row_number等于1的信息
您可能感兴趣的文章:
如您对本文有疑问或者有任何想说的，请点击进行留言回复，万千网友为您解惑！
网友评论