1.有以下订单表Order(简表,XX...代表的数字一样,money和orderID与业务无关忽略处理,表数据在千万条):
id | username | mac | ip | money | orderID |
1 | mike | 12:XX... | 1.2.3.4 | money | orderID |
2 | leo | EC:XX... | 120.145.2.1 | money | orderID |
3 | mike | EC:XX... | 120.145.2.1 | money | orderID |
4 | mike | EC:XX... | 120.145.2.5 | money | orderID |
5 | kitty | EC:XX... | 120.145.2.3 | money | orderID |
6 | leo | 34:XX... | 2.3.4.5 | money | orderID |
7 | leo | DD:XX... | 110.120.122.119 | money | orderID |
8 | jhon | 56:XX.. | 5.6.7.8 | money | orderID |
9 | jhon | 56:XX.. | 5.6.7.8 | money | orderID |
10 | ben | DD:XX.. | 110.120.122.119 | money | orderID |
11 | ben | 78:XX.. | 7.8.9.10 | money | orderID |
2.异地代充设备的定义:在同一设备(MAC)登录两个用户以上,并且这些用户中至少有两个用户在别处登录
3.上表中有两个异地代充设备,分别为EC:XX和DD:XX,其中mike,leo,kitty找了代充机构EC:XX,其中leo同时找了代充机构DD:XX, ben也找了代充机构DD:XX
4.如何找出类似EC:XX和DD:XX的设备?
谢谢邀请。用find_in_set
函数试试看
SELECT id FROM table WHERE find_in_set('EC:XX', mac) OR find_in_set('DD:XX', mac);
SELECT mac FROM (
SELECT DISTINCT mac, username FROM ORDER
) AS tmp
GROUP BY mac
having count(1) > 1
不知道有没有写错,没有测试,但估计性能不会很好,毕竟涉及到disintct
和group by
操作。