英文:
How do I select all values which satisfy certain aggregate properties?
问题
我有一个名为"Customers"的表，包括以下内容：
- CustomerName
- Country
- City
我尝试列出所有来自至少有两名顾客的城市的顾客姓名。
这是我的初始尝试：
SELECT CustomerName, City
FROM Customers
GROUP BY City
HAVING COUNT(City) > 1
这是我得到的结果：
| CustomerName | City |
|---|---|
| Person A | New York |
| Person C | Los Angeles |
在这里，Person A是来自纽约的人，出现在表格的顶部，类似于Person B。但是，我想要的是列出所有来自纽约和洛杉矶的顾客。
当我尝试：
SELECT COUNT(CustomerName), City
FROM Customers
GROUP BY City
HAVING COUNT(City) > 1
我得到了以下结果：
| COUNT(CustomerName) | City |
|---|---|
| 3 | New York |
| 5 | Los Angeles |
这意味着代码是正常工作的，只是我的原始代码只显示了纽约和洛杉矶的顶部一个人。我该如何解决这个问题？
英文:
Say, I have a table (named "Customers") which consists of:
- CustomerName
- Country
- City
I am trying to list the names of all customers from cities where there are at least two customers.
This is my initial attempt:
SELECT CustomerName, City
FROM Customers
GROUP BY City
HAVING COUNT(City) > 1
This is the result that I got:
| CustomerName | City |
|---|---|
| Person A | New York |
| Person C | Los Angeles |
Here, Person A is a person from NY who appears on the top of the table and similar for Person B. However, what I wanted was the listing of all customers from New York and LA.
When I tried:
SELECT COUNT(CustomerName), City
FROM Customers
GROUP BY City
HAVING COUNT(City) > 1
I had
| COUNT(CustomerName) | City |
|---|---|
| 3 | New York |
| 5 | Los Angeles |
This means that the code is working properly, except that my original code only displays a person on top of the table from NY and LA. How do I resolve this issue?
答案1
得分: 0
我已经将选择部分中的城市移除，因为您说您只想要客户名称。
SELECT a.CustomerName
FROM Customers a
WHERE (
SELECT COUNT(b.CustomerName)
FROM Customers b
WHERE b.City = a.City
) > 1
ORDER BY a.CustomerName
英文:
How about this? I've taken the city out of the select part since you said you just wanted customer names.
SELECT a.CustomerName
FROM Customers a
WHERE (
SELECT COUNT(b.CustomerName)
FROM Customers b
WHERE b.City = a.City
) > 1
ORDER BY a.CustomerName
答案2
得分: 0
选择具有超过1名客户的城市，并使用该列表来选择客户：
SELECT cst.CustomerName
FROM Customers cst
WHERE Cst.City in (
-- 所有至少有两名客户的城市
SELECT CstGE2.City
FROM Customers CstGE2
GROUP BY CstGE2.City
HAVING count(*) >= 2
)
英文:
Get cities with more than 1 customer in a subquery, and use that list to select the customers:
SELECT cst.CustomerName
FROM Customers cst
WHERE Cst.City in (
-- All cities where there are at least two customers
SELECT CstGE2.City
FROM Customers CstGE2
GROUP BY CstGE2.City
HAVING count(*) >= 2
)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论