用于查找 count > 1 的记录的 SQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7151401/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL query for finding records where count > 1
提问by Benjamin Muschko
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.
我有一张名为PAYMENT. 在这个表中，我有一个用户 ID、一个帐号、一个邮政编码和一个日期。我想查找每天使用相同帐号进行多次付款的所有用户的所有记录。
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.
您将如何在 SQL 查询中表达这一点？我想自我加入但由于某种原因我的计数是错误的。
回答by Conrad Frix
Use the HAVINGclause and GROUP By the fields that make the row unique
使用HAVING子句和 GROUP By 使行唯一的字段
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
UpdateIf 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
更新如果您只想包含那些具有不同 ZIP 的内容，您可以先获得一个不同的集合，然后执行 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
回答by user4019456
Try this query:
试试这个查询：
SELECT column_name
FROM table_name
GROUP BY column_name
HAVING COUNT(column_name) = 1;
回答by onedaywhen
I wouldn't recommend the HAVINGkeyword for newbies, it is essentially for legacy purposes.
我不会HAVING为新手推荐关键字，它本质上是为了遗留目的。
I am not clear on what is the key for this table (is it fully normalized, I wonder?), consequently I find it difficult to follow your specification:
我不清楚这个表的关键是什么（我想知道它是完全标准化的吗？），因此我发现很难遵循你的规范：
I would like to find all records for all users that have more than one payment per day with the same account number... Additionally, there should be a filter than only counts the records whose ZIP code is different.
我想查找每天使用相同帐号进行多次付款的所有用户的所有记录......此外，应该有一个过滤器，而不是只计算邮政编码不同的记录。
So I've taken a literal interpretation.
所以我采取了字面解释。
The following is more verbose but could be easier to understand and therefore maintain (I've used a CTEfor the table PAYMENT_TALLIESbut it could be a VIEW:
以下内容更冗长，但可能更容易理解和维护（我在表格中使用了CTE，PAYMENT_TALLIES但它可能是VIEW：
WITH PAYMENT_TALLIES (user_id, zip, tally)
AS
(
SELECT user_id, zip, COUNT(*) AS tally
FROM PAYMENT
GROUP
BY user_id, zip
)
SELECT DISTINCT *
FROM PAYMENT AS P
WHERE EXISTS (
SELECT *
FROM PAYMENT_TALLIES AS PT
WHERE P.user_id = PT.user_id
AND PT.tally > 1
);
回答by iryndin
create table payment(
user_id int(11),
account int(11) not null,
zip int(11) not null,
dt date not null
);
insert into payment values
(1,123,55555,'2009-12-12'),
(1,123,66666,'2009-12-12'),
(1,123,77777,'2009-12-13'),
(2,456,77777,'2009-12-14'),
(2,456,77777,'2009-12-14'),
(2,789,77777,'2009-12-14'),
(2,789,77777,'2009-12-14');
select foo.user_id, foo.cnt from
(select user_id,count(account) as cnt, dt from payment group by account, dt) foo
where foo.cnt > 1;
