如何在子查询结果上使用MAX()? [英] How to use MAX() on a subquery result?
问题描述
我是Oracle和SQL领域的新手.我在查询中有一个小问题,我一生无法解决,我花了几个小时尝试不同的方法,但无法获得预期的结果.所以这是我的查询:
I am new to Oracle and the SQL world. I have a slight issue with a query that I cannot figure out for the life of me, I have spent a few hours trying different approaches and I cannot get the result I expect. So heres my query:
SELECT *
from(Select membership.mem_desc,membership.mem_max_rentals,membership_history.mem_type,
count(membership_history.MEM_TYPE) as membership_count
from membership_history
JOIN membership ON membership.mem_type = membership_history.mem_type
group by (membership_history.mem_type,membership.mem_desc,membership.mem_max_rentals)
) g
WHERE g.membership_count = (select MAX(membership_count) from g);
因此内部查询可以正常工作,并返回两个结果.现在,我有了这两个值,我试图找出如何返回具有最大Membership_count值的行,这是我一直陷入困境的地方.在上面的查询中,我尝试在where子句中使用MAX(),但在该选择项中,我不断收到错误找不到表"(意思是"g").所以我的问题是如何在子查询的结果上使用MAX()函数?任何想法或建议将不胜感激!!!!!
So the inner query works perfectly and returns two results. Now that I have these two values I am trying to figure out how to return the row with the maximum value of membership_count which Is where I keep getting stuck. In the above query I tried using the MAX() in the where clause but inside that select I keep getting the error 'table not found'(meaning 'g'). So my question is how do I use the MAX() function on the results of my subquery? Any thoughts or suggestions would be greatly appreciated!!!!!
推荐答案
您不需要找到最大值的子查询.
反而, ;您只需对行进行排序后就需要第一行:
You don't need the subquery that finds the maximum value.
Instead, ; you just need the first row after having ordered the rows:
select * from (
select
membership.mem_desc,
membership.mem_max_rentals,
membership_history.mem_type,
count(membership_history.MEM_TYPE) as membership_count
from membership_history
JOIN membership ON membership.mem_type = membership_history.mem_type
group by (membership_history.mem_type,membership.mem_desc,membership.mem_max_rentals)
ORDER BY 4 DESC -- Added this line
) g
WHERE ROWNUM = 1. -- Added this line
这篇关于如何在子查询结果上使用MAX()?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!