SQL查询查找count> 1 [英] SQL query for finding records where count > 1

问题描述

我有一个名为 PAYMENT 的表。在此表格中，我有一个用户ID，帐号，邮政编码和日期。我想查找所有使用相同帐号每天都有多笔付款的用户的所有记录。

I have a table named PAYMENT. Within this table I have a user ID, an account number, a ZIP code and a date. I would like to find all records for all users that have more than one payment per day with the same account number.

UPDATE：此外，应该有一个过滤器，而不只是对邮政编码不同的记录进行计数。

UPDATE: Additionally, there should be a filter than only counts the records whose ZIP code is different.

这是表格的样子：

| user_id | account_no | zip   |      date |
|       1 |        123 | 55555 | 12-DEC-09 | 
|       1 |        123 | 66666 | 12-DEC-09 |
|       1 |        123 | 55555 | 13-DEC-09 |
|       2 |        456 | 77777 | 14-DEC-09 |
|       2 |        456 | 77777 | 14-DEC-09 |
|       2 |        789 | 77777 | 14-DEC-09 |
|       2 |        789 | 77777 | 14-DEC-09 |

结果应类似于：

| user_id | count |
|       1 |     2 |

如何在SQL查询中表达？

How would you express this in a SQL query? I was thinking self join but for some reason my count is wrong.

推荐答案

使用 HAVING 子句和GROUP由使行唯一的字段

Use the HAVING clause and GROUP By the fields that make the row unique

下面将找到

所有使用相同帐号每天付款超过一次的用户

all users that have more than one payment per day with the same account number

SELECT 
 user_id ,
 COUNT(*) count
FROM 
 PAYMENT
GROUP BY
 account,
 user_id ,
 date
Having
COUNT(*) > 1

更新
如果您只希望有一个不同的ZIP你可以先得到一个不同的集合，然后执行你HAVING / GROUP BY

Update If you want to only include those that have a distinct ZIP you can get a distinct set first and then perform you HAVING/GROUP BY

 SELECT 
    user_id,
    account_no , 
    date,
        COUNT(*)
 FROM
    (SELECT DISTINCT
            user_id,
            account_no , 
            zip, 
            date
         FROM
            payment 

        ) 
        payment
 GROUP BY

    user_id,
    account_no , 

    date
HAVING COUNT(*) > 1

这篇关于SQL查询查找count> 1的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！