php - MySql查询重复字段

Question

我有包含 fowling 内容的 sql 表:

id | joined              | ip
---------------------------------------
 1 | 2014-03-20 10:01:51 | 214.254.22.1
 2 | 2014-03-20 11:01:51 | 214.254.2.1
 5 | 2014-03-20 12:01:51 | 214.254.12.1
 8 | 2014-03-20 13:01:51 | 214.254.22.1

例如，我想在 2014-03-20 10:00:00 和 ip 之后找到 joined 的所有结果超过一次在所有记录中

所以在这种情况下我的结果应该是:

 1 | 2014-03-20 10:01:51 | 214.254.22.1

因为它是在 10:00:00 之后加入的，并且在数据库中找到了 2 次 ip 214.254.22.1。

这种查询是否可能？

Answer 1

试试这个:

mysql> create table `t` (`id`  int, `joined` datetime, `ip` char(20));
Query OK, 0 rows affected (0.43 sec)

mysql> insert into `t` values
    -> (1, '2014-03-20 10:01:51', '214.254.22.1'),
    -> (2, '2014-03-20 11:01:51', '214.254.2.1'),
    -> (5, '2014-03-20 12:01:51', '214.254.12.1'),
    -> (8, '2014-03-20 13:01:51', '214.254.22.1');
Query OK, 4 rows affected (0.12 sec)
Records: 4  Duplicates: 0  Warnings: 0

[answer]查询:

mysql> select *, count(`ip`) as total_ip  
    -> from `t` 
    -> where joined > '2014-03-20 10:00:00' 
    -> group by ip 
    -> having total_ip > 1 ;
+------+---------------------+--------------+----------+
| id   | joined              | ip           | total_ip |
+------+---------------------+--------------+----------+
|    1 | 2014-03-20 10:01:51 | 214.254.22.1 |        2 |
+------+---------------------+--------------+----------+
1 row in set (0.00 sec)