英文:
How to write a SQL query that "iterates" over values from another query
问题
我有一个类似这样的查询（我简化了以确保清晰）：
SELECT avg(p.age)
FROM Person p
WHERE p.name = "John"
GROUP BY name
请注意，可能有50个名为John的人在Person表中，所以我正在获取所有这些人的年龄的平均值。
但我真正想做的是使查询返回另一个查询返回的每个名称的avg(p.age)，比如{John, James, Sam, Mary}。当然，我有一个可以返回所有名称的查询，例如（再次简化）：
SELECT name
FROM Names
[因为在我的实际数据库中，名称存储在不同的表中，而不是Person表]
也就是说，我希望一个查询的输出类似于以下内容，而不必运行多个带有每个名称硬编码的查询：
John 37.5
Mary 27.9
James 42.0
Sam 17.6
谢谢您的建议！
我尝试过子查询或递归查询，但无法完全得到我所需要的结果。
英文:
I have a query like this (I am simplifying to try to be clear):
SELECT avg(p.age)
FROM Person p
WHERE p.name = "John"
GROUP BY name
Note there might be 50 people named John in Person, so I am getting the average of all their ages.
But what I really want to do is to have the query return the avg(p.age) for each name returned from another query, say {John, James, Sam, Mary}. Of course I have a query that can return all the names, such as (again, simplifying)
SELECT name
FROM Names
[Since in my real like database, Names are stored in a different table than Person]
That is, I want output from the one query to be something like the following and not have to run multiple queries with the name hard-coded in to each one:
John 37.5
Mary 27.9
James 42.0
Sam 17.6
Thank you for your thoughts!
I tried subqueries or recursive queries, but could not quite get what I needed.
答案1
得分: 3
根据您的描述，以下代码应该提供所需的输出：
SELECT p.name, AVG(p.age)
FROM Person p
WHERE p.name IN (SELECT name
FROM Names)
GROUP BY p.name
WHERE 子句将仅选择嵌套子查询检索到的名称。然后，GROUP BY 子句将计算仅选定名称的平均年龄。
英文:
From what you are describing, the following code should provide the desired output:
SELECT p.name, AVG(p.age)
FROM Person p
WHERE p.name IN (SELECT name
FROM Names)
GROUP BY p.name
The WHERE clause will select only the names retrived by the nested subquery. Then the GROUP BY clause will compute the average age of just the selected names.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论