也就是说你没有另外的帐号数据,只要对有登录过的帐号做这个统计就可以了是吗? 这样的语句试试: mysql> SELECT * FROM loglist; +---------+---------------------+ | Account | ConnectTime | +---------+---------------------+ | buggy | 2011-05-17 22:22:00 | | buggy | 2012-05-17 23:59:15 | | buggy1 | 2011-05-17 23:59:15 | | buggy1 | 2011-02-17 23:59:15 | | buggy2 | 2011-02-17 23:59:15 | +---------+---------------------+ 5 rows in set (0.00 sec)mysql> SELECT COUNT(*) -> FROM -> ( -> SELECT Account -> FROM loglist -> GROUP BY Account -> HAVING MAX(ConnectTime) < (NOW() + INTERVAL -1 MONTH) -> ) AS T; +----------+ | COUNT(*) | +----------+ | 2 | +----------+ 1 row in set (0.00 sec)
可能这个会更好理解一些: mysql> SELECT COUNT(*) -> FROM -> ( -> SELECT Account, MAX(ConnectTime) AS Ti -> FROM loglist -> GROUP BY Account -> ) AS T -> WHERE Ti < (NOW() + INTERVAL -1 MONTH); +----------+ | COUNT(*) | +----------+ | 2 | +----------+ 1 row in set (0.00 sec)
如果是这样的话,那就还需要一个所有帐号的表,是否有一个帐号信息表?
回复3楼:每个帐号有多条记录,ConnectTime是每一次登录的时间,有帐号信息,表中一起的Account就是帐号。
比如
mysql> select Account,ConnectTime from loggame.loglist where ConnectTime >= curdate()-1 and ConnectTime < curdate() and Account = 'buggy';
+-------------+---------------------+
| Account | ConnectTime |
+-------------+---------------------+
| buggy | 2012-05-17 22:22:00 |
| buggy | 2012-05-17 23:59:15 |
+-------------+---------------------+回复2楼要求结果:一个月未登录的人数和这样就行了
+--------------------+
| count(Account) |
+--------------------+
| 123456 |
+--------------------+
这样的语句试试:
mysql> SELECT * FROM loglist;
+---------+---------------------+
| Account | ConnectTime |
+---------+---------------------+
| buggy | 2011-05-17 22:22:00 |
| buggy | 2012-05-17 23:59:15 |
| buggy1 | 2011-05-17 23:59:15 |
| buggy1 | 2011-02-17 23:59:15 |
| buggy2 | 2011-02-17 23:59:15 |
+---------+---------------------+
5 rows in set (0.00 sec)mysql> SELECT COUNT(*)
-> FROM
-> (
-> SELECT Account
-> FROM loglist
-> GROUP BY Account
-> HAVING MAX(ConnectTime) < (NOW() + INTERVAL -1 MONTH)
-> ) AS T;
+----------+
| COUNT(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)
mysql> SELECT COUNT(*)
-> FROM
-> (
-> SELECT Account, MAX(ConnectTime) AS Ti
-> FROM loglist
-> GROUP BY Account
-> ) AS T
-> WHERE Ti < (NOW() + INTERVAL -1 MONTH);
+----------+
| COUNT(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)