如何在MySQL中使用GROUP BY结果后的WHERE子句

Question

我有下面这张表：

    mysql> select username,authdate from radpostauth;
+------------+---------------------+
| username   | authdate            |
+------------+---------------------+
| timetest   | 2013-05-21 22:44:46 |
| timetest   | 2013-05-21 23:54:20 |
| coconeja   | 2013-05-22 00:01:42 |
| coconeja   | 2013-05-22 00:06:35 |
| coconeja   | 2013-05-25 22:35:34 |
| timetest   | 2013-05-25 23:04:54 |
| distom11   | 2013-05-25 23:10:47 |
| distom11   | 2013-05-25 23:16:42 |
| test       | 2013-05-25 23:45:16 |
| pepe       | 2013-05-26 00:07:00 |
| doce       | 2013-05-26 00:46:48 |
| 6096753968 | 2013-05-26 01:42:30 |
| 2269664468 | 2013-05-26 01:43:57 |
| 2076877624 | 2013-05-26 02:01:53 |
| 4446830988 | 2013-05-26 02:02:28 |
| 2076877624 | 2013-05-26 02:08:53 |
| 3906187975 | 2013-05-26 22:00:30 |
| 3906187975 | 2013-05-26 22:21:44 |
| kk         | 2013-05-26 22:32:20 |
| kk         | 2013-05-26 22:44:19 |
| 160059817  | 2013-05-27 00:53:56 |
| yibced9    | 2013-05-27 13:32:00 |
| yibced9    | 2013-05-27 13:37:53 |
| yibced9    | 2013-05-27 13:38:01 |
| yibced9    | 2013-05-27 13:38:02 |
| yibced9    | 2013-05-27 13:38:03 |
| yibced9    | 2013-05-27 13:38:39 |
| yibced9    | 2013-05-27 13:38:40 |
| yibced9    | 2013-05-27 13:38:41 |
| yibced9    | 2013-05-27 13:44:40 |

我希望找到最近12小时内首次访问的“用户名”。

我知道如何使用以下查询获取每个用户的第一次登录时间：

    mysql> select username,min(authdate) from radpostauth group by username;
+------------+---------------------+
| username   | min(authdate)       |
+------------+---------------------+
| 160059817  | 2013-05-27 00:53:56 |
| 2076877624 | 2013-05-26 02:01:53 |
| 2269664468 | 2013-05-26 01:43:57 |
| 3906187975 | 2013-05-26 22:00:30 |
| 4446830988 | 2013-05-26 02:02:28 |
| 6096753968 | 2013-05-26 01:42:30 |
| coconeja   | 2013-05-22 00:01:42 |
| distom11   | 2013-05-25 23:10:47 |
| doce       | 2013-05-26 00:46:48 |
| kk         | 2013-05-26 22:32:20 |
| pepe       | 2013-05-26 00:07:00 |
| test       | 2013-05-25 23:45:16 |
| timetest   | 2013-05-21 22:44:46 |
| yibced9    | 2013-05-27 13:32:00 |
+------------+---------------------+

现在我正在尝试通过以下方式获取这些用户名：

select *
from radpostauth
where (
    select username,min(authdate)
    from radpostauth group by username
    ) > timediff( now(),maketime(12,0,0,) );

但显然它不起作用...

- David Mart

Answer 1

select username, min(authdate) 
from radpostauth 
group by username
having min(authdate) > NOW() - INTERVAL 12 HOUR

在SQL中，WHERE限制行，而HAVING限制组。

- Bill Karwin

---

更像是where在扫描表时在行级别操作，而having在所有聚合函数计算结果后，在结果集级别操作。 - Marc B

Answer 2

完美的答案是Bill提供的，但为了完备起见，David的尝试值得纠正:

SELECT *
FROM (
    SELECT username, MIN(authdate) as min_authdate
    FROM radpostauth
    GROUP BY username
) T
WHERE min_authdate > NOW() - INTERVAL 12 HOUR;

- Olivier Coilland