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

deci

问题：  同一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

仅供参考

xuanhao

逆风的方向，更适合飞翔。我不怕万人阻挡，只怕自己投降。