用于查找 count > 记录的 SQL 查询1 [英] SQL query for finding records where count > 1
问题描述
我有一个名为 PAYMENT
的表.在这个表中,我有一个用户 ID、一个帐号、一个邮政编码和一个日期.我想查找每天使用相同帐号进行多次付款的所有用户的所有记录.
更新:此外,应该有一个过滤器,而不是只计算邮政编码不同的记录.
表格是这样的:
<前>|用户 ID |account_no |拉链|日期 ||1 |123 |55555 |2009 年 12 月 12 日 ||1 |123 |66666 |2009 年 12 月 12 日 ||1 |123 |55555 |2009 年 12 月 13 日 ||2 |第456话77777 |09 年 12 月 14 日 ||2 |第456话77777 |09 年 12 月 14 日 ||2 |第789话77777 |09 年 12 月 14 日 ||2 |第789话77777 |09 年 12 月 14 日 |结果应该类似于:
<前>|用户 ID |计数 ||1 |2 |您将如何在 SQL 查询中表达这一点?我想自加入但由于某种原因我的计数是错误的.
使用 HAVING 子句和 GROUP By 使行唯一的字段
下面会找到
<块引用>每天使用同一账号支付超过一笔的所有用户
SELECT用户身份 ,COUNT(*) 计数从支付通过...分组帐户,用户身份 ,日期有计数(*) >1
更新如果您只想包含那些具有不同 ZIP 的内容,您可以先获得一个不同的集合,然后执行 HAVING/GROUP BY
选择用户身份,户口号码 ,日期,数数(*)从(选择不同用户身份,户口号码 ,压缩,日期从支付)支付通过...分组用户身份,户口号码 ,日期有计数(*) >1
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: Additionally, there should be a filter than only counts the records whose ZIP code is different.
This is how the table looks like:
| 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 |
The result should look similar to this:
| user_id | count | | 1 | 2 |
How would you express this in a SQL query? I was thinking self join but for some reason my count is wrong.
Use the HAVING clause and GROUP By the fields that make the row unique
The below will find
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
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
这篇关于用于查找 count > 记录的 SQL 查询1的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!