mysql - MySQL从列a中为每个不同的列b选择所有不同的值，其中计数不同的a> 1

我有一个表，用于存储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

最佳答案

好吧，我想你的意思是…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

关于mysql - MySQL从列a中为每个不同的列b选择所有不同的值，其中计数不同的a> 1，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/10640386/