如何在子查询结果上使用 MAX()?

2022-01-23 00:00:00 max sql subquery oracle aggregate-functions

我是 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);

所以内部查询完美运行并返回两个结果.现在我有了这两个值，我试图弄清楚如何返回具有最大成员计数的行，这是我一直卡住的地方.在上面的查询中，我尝试在 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!!!!!

推荐答案

你不需要找到最大值的子查询.
反而， ;在 ordered 行之后，您只需要 first 行:

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

相关文章