重复字段的MySql查询

Question

我有一个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

例如，在joined和2014-03-20 10:00:00和ip在所有记录中不止一次找到之后，我希望找到所有的结果。

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

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

因为它是在10: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

应答查询：

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)

Answer 2

可以使用Having子句对特定聚合进行筛选，如下所示：

select
    id,
    joined,
    count(ip)
from
    yourTable
where
    joined > yourDate
group by
    id,
    joined
having
    count(ip) > 1

您可以在这里找到更多信息：http://dev.mysql.com/doc/refman/5.0/en/group-by-extensions.html

Answer 3

试试这个：

SELECT id, joined, ip, count(ip) 
FROM tablename
WHERE joined > date  
HAVING count(ip) > 2