MySQL 为每个不同的列 b 从列 a 中选择所有不同的值，其中 count distinct a >1

Question

我有一张表用于存储对 radius 服务器的所有访问请求，如下所示:

describe radius.loginattempts;
+------------------+------------------+------+-----+---------+----------------+
| Field            | Type             | Null | Key | Default | Extra          |
+------------------+------------------+------+-----+---------+----------------+
| loginattempt_key | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| log_date         | int(10) unsigned | NO   |     | NULL    |                |
| result           | tinyint(1)       | YES  |     | NULL    |                |
| username         | text             | YES  |     | NULL    |                |
| mac              | text             | YES  |     | NULL    |                |
+------------------+------------------+------+-----+---------+----------------+

每当有人尝试登录时，无论是否成功，都会在此表中创建一个日志条目。

我想要做的是获取所有有两个或更多用户尝试登录的设备的列表，以及每个设备的所有不同用户名。

所以，如果我有一些看起来像这样的数据:

select * from loginattempts limit 8;
+------------------+------------+--------+----------+-------------------+
| loginattempt_key | log_date   | result | username | mac               |
+------------------+------------+--------+----------+-------------------+
|                1 | 1337035334 |      1 | kclark   | 11:11:11:11:11:11 |
|                2 | 1337035620 |      1 | kclark   | 22:22:22:22:22:22 |
|                3 | 1337035681 |      0 | guest    | 33:33:33:33:33:33 |
|                4 | 1337035740 |      1 | guest    | 22:22:22:22:22:22 |
|                5 | 1337037782 |      1 | rfogarty | 44:44:44:44:44:44 |
|                6 | 1337037789 |      1 | jsmith   | 44:44:44:44:44:44 |
|                7 | 1337037790 |      1 | jsmith   | 44:44:44:44:44:44 |
|                8 | 1337037791 |      1 | jsmith   | 44:44:44:44:44:44 |
+------------------+------------+--------+----------+-------------------+

我想要一个返回如下内容的神奇查询:

+-------------------+----------+
| mac               | username |
+-------------------+----------+
| 22:22:22:22:22:22 | kclark   |
| 22:22:22:22:22:22 | guest    |
| 44:44:44:44:44:44 | rfogarty |
| 44:44:44:44:44:44 | jsmith   |
+-------------------+----------+

这最终将在 PHP 中实现，我可以使用两个查询和一个 for 循环轻松完成，但作为一个思想实验，我一直在尝试考虑如何使用单个查询和一些奇特的连接(甚至子查询)，但就是无法理解它...

mysql -V
mysql  Ver 14.14 Distrib 5.1.52, for unknown-linux-gnu (x86_64) using readline 5.1

Answer 1

好的，我想你的意思是... http://sqlfiddle.com/#!3/b6100/6

给定

create table loginattempts  
(
  login_key int,
  mac varchar(12) null,
  username varchar(12) null
)

insert into loginattempts values (1, 111, 'peter')
insert into loginattempts values (2, 111, 'bob')
insert into loginattempts values (3, 222, 'geoff')
insert into loginattempts values (4, 222, 'lisa')
insert into loginattempts values (1, 333, 'peter')

你需要这个查询

   select mac, username
   from loginattempts
   where
    mac in
    (
      select mac
      from loginattempts
      group by mac
      having count(*) > 1
     )

给你

MAC USERNAME
111 peter
111 bob
222 geoff
222 lisa