mysql: 比较同一张表的相邻两行的某一字段的值

时间：2021-03-04 14:47:02

问题：  同一IP的MAC地址变更次数统计并按照降序排列

例子：

IP MAC
192.168.1.100 00:22:00:33:00:11
192.168.1.100 00:22:00:33:ff:ff
192.168.1.100 00:22:00:33:00:11
192.168.1.100 00:22:00:33:ff:ff
192.168.1.100 00:22:00:33:ff:ff
192.168.1.100 00:22:00:33:00:11


上述6条记录中总共变更了4次

 

思路：
同一IP，降序排列，比较相邻行的的MAC大小并统计，然后按照统计数字降序排序

table:ip_mac

ip varchar(20)

mac varchar(17)

 

sql:

select sum(ABS(IFNULL(STRCMP(a.mac,b.mac), 1 ))) from 
(SELECT im.*, @num := @num + 1 as row_number from (SELECT @num :=0) r,
(select ip,mac from ip_mac order by ip ) im
) as a 
left join
(SELECT im2.*, @num2 := @num2 + 1 as row_number from (SELECT @num2 :=0) r2,
(select ip,mac from ip_mac order by ip ) im2
) as b on a.row_number + 1= b.row_number

仅供参考

 

感谢其他作者的分享，才有我这篇文章

http://www.iteye.com/topic/322307

 

 

 

 

标签：

相关文章