带有重复记录的 SQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3152224/
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 with duplicate records
提问by nav100
I am trying to write a query in SQL server to find out if there are any multiple rows for each customer by customerID. Please let me know.
我正在尝试在 SQL Server 中编写一个查询，以通过 customerID 找出每个客户是否有多行。请告诉我。
Here is the table structure
这是表结构
Customer table
-----------------------------------------
orderID CustName CustomerID
---------------------------------------
100 test 123456
101 test 123456
Orders table
------------------------------------
pID OrderID
-----------------------------------
1 100
2 101
回答by RedFilter
回答by FrustratedWithFormsDesigner
To see how many of each customer you have:
要查看您拥有的每个客户的数量：
SELECT COUNT(*), CustName, CustomerID
from Customer
Group by CustName, CustomerID
You can use a havingclause to limit to just duplicates:
您可以使用having子句来限制重复：
SELECT COUNT(*), CustName, CustomerID
from Customer
Group by CustName, CustomerID
having count(*) > 1
UPDATE
更新
To get those with successful orders:
要获得成功订单的人：
select count(*), CustName, CustomerID
from(
SELECT CustName, CustomerID
from Customer, orders
where customer.orderID = orders.orderID
and orders.success = 1) subquery
group by subquery.CustName, subquery.CustomerID
having count(*) > 1;
回答by Pablo Santa Cruz
select CustomerID, count(1)
from Customer
group by CustomerID
having count(1) > 1
