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

How to use MAX() on a subquery result?

提问by user1916441

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:

我是 Oracle 和 SQL 世界的新手。我对一个我一生无法弄清楚的查询有一个小问题，我花了几个小时尝试不同的方法，但无法得到我期望的结果。所以继承人我的查询：

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

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!!!!!

所以内部查询完美运行并返回两个结果。现在我有了这两个值，我想弄清楚如何返回具有最大值 members_count 的行，这是我一直卡住的地方。在上面的查询中，我尝试在 where 子句中使用 MAX() 但在该 select 中，我不断收到错误“找不到表”（意思是“g”）。所以我的问题是如何对子查询的结果使用 MAX() 函数？任何想法或建议将不胜感激！！！！！！

回答by Bohemian

You don't need the subquery that finds the maximum value.
Instead, ; you just need the firstrow after having orderedthe 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

回答by user3552157

This is all good SQL.

这都是很好的 SQL。

The best way to find a column value using the max primary key column value is:

使用最大主键列值查找列值的最佳方法是：

SELECT .... from tablename
WHERE ... AND
  (SELECT max(primary key name) FROM tablename WHERE .... ) = primary_key_name

this example will return a scalar value.

这个例子将返回一个标量值。

回答by bxdobs

I have had a similar issue within an Access Query ... based on some of the comments in this thread, are we to understand that the ACCESS MAX function should only be used on/with Primary Key Fields? ... basically, I have a table that defines Addresses relating to an Entity, BUT, with a slight twist; 1) once saved, no record is allowed to be deleted or changed (meaning an Entity could have multiple address Records), 2) any current Entity Address can either have; HOME (1 Record) or HOME and MAIL (2 records) ... each Address record has an (ID) (autonumber Primary KEY) a HOME ID (HID) matching the HOME record's (ID) (HID is NOT a Primary KEY and is NOT unique) and an Entity ID (EID) ... so ... when I attempt to use a sub-query to get the max(HID) value for a given Entity, I only want the current Address returned ... what I found by using MAX in a sub-query is it either fails to run because ACCESS incorrectly thinks it has too many matching records for the sub-query ... or ... it incorrectly returns rows it shouldn't be returning.

我在 Access Query 中遇到了类似的问题......根据该线程中的一些评论，我们是否理解 ACCESS MAX 函数应该只用于/与主键字段一起使用？...基本上，我有一个表格，它定义了与实体相关的地址，但略有不同；1）一旦保存，不允许删除或更改任何记录（意味着一个实体可以有多个地址记录），2）任何当前实体地址都可以有；HOME（1 条记录）或 HOME 和 MAIL（2 条记录）...每个地址记录都有一个（ID）（自动编号主键）一个与 HOME 记录的（ID）匹配的 HOME ID (HID)（HID 不是主键，并且不是唯一的）和一个实体 ID (EID) ......所以......当我尝试使用子查询来获取给定实体的 max(HID) 值时，我只希望返回当前地址......

The only way I found to get around this issue was to replace the max sub-query with a Max Function which returns the MAX HID Value for an Entity (the function uses the sub-query statement with DAO logic within VBA)

我发现解决这个问题的唯一方法是用 Max 函数替换 max 子查询，该函数返回实体的 MAX HID 值（该函数在 VBA 中使用带有 DAO 逻辑的子查询语句）

Here is the HOME sub-query which is part of the main Query (the main query needs to return one row per entity as; EID, Home.Address, Mail.Address)

这是 HOME 子查询，它是主查询的一部分（主查询需要为每个实体返回一行；EID、Home.Address、Mail.Address）

Select *
From tbAddresses As tba1
Where tba1.aType = "Home"
  And tba1.HID = (Select MAX(tba2.HID) 
                      From tbAddresses As tba2 
                     Where tba1.EID = tba2.EID)

The main query only works properly when the Where clauses both Home and Mail (not shown) are replaced with the function below. If the MAX sub-query is included as above, it doesn't work.

只有当 Home 和 Mail（未显示）的 Where 子句被下面的函数替换时，主查询才能正常工作。如果 MAX 子查询包含在上面，则不起作用。

So if the MAX function requires a PRIMARY KEY field to work, this might explain why my queries are failing, although, that would sound like a major limitation.

因此，如果 MAX 函数需要 PRIMARY KEY 字段才能工作，这可能解释了为什么我的查询失败，尽管这听起来像是一个主要限制。

Where tba1.HID = fnGetMaxHID(tba1.EID) 

Here is some test data which should only return 3 rows

这是一些应该只返回 3 行的测试数据

ID    HID    EID   aType  Address
 1      1    100   Home   Blah 1
 2      2    101   Home   Blah 2
 3      2    101   Mail   PO Box Blah 0
 4      4    102   Home   Blah 3
 5      5    101   Home   Blah 4

One last note, several versions of Access Pro; 2002, 2003, 2016 were tested and all produce the same results. So this issue is either an inherent quirk of the max function or some kind of bug which has possibly been ignored or gone unnoticed? ... the function works as a workaround for me but may not work for others so would be nice if the MAX function was clarified.

最后一点，Access Pro 的几个版本；2002、2003、2016 年进行了测试，都产生了相同的结果。所以这个问题要么是 max 函数的固有怪癖，要么是某种可能被忽略或未被注意到的错误？...该函数对我来说是一种解决方法，但对其他人可能不起作用，所以如果 MAX 函数得到澄清，那就太好了。

回答by Mindstick

Here, an aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference in SQL Server.

此处，聚合可能不会出现在 WHERE 子句中，除非它位于包含在 HAVING 子句或选择列表中的子查询中，并且被聚合的列是 SQL Server 中的外部引用。

For the demonstration, we have a table named ‘Info' with some records.

为了演示，我们有一个名为“Info”的表，其中包含一些记录。

--Select records from info
SELECT * FROM INFO

ScreenShot

截屏

Problem Statement: Find all the details of INFO for the max id.

问题陈述：查找最大 id 的 INFO 的所有详细信息。

SELECT * FROM INFO WHERE ID = MAX(ID)

When he executed the above script it gave him the following error:

当他执行上述脚本时，它给了他以下错误：

Message 147, Level 15, State 1, Line 3
The aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

消息 147，级别 15，状态 1，第 3 行
聚合可能不会出现在 WHERE 子句中，除非它位于包含在 HAVING 子句或选择列表中的子查询中，并且被聚合的列是外部引用。

He was not able to resolve this problem, even though the solution was given in the query description itself.

他无法解决这个问题，即使在查询描述中给出了解决方案。

Due to a lack of experience, he came up with another version of the above query based on the error message.

由于缺乏经验，他根据错误信息想出了上述查询的另一个版本。

SELECT * FROM INFO HAVING ID = MAX(ID)

Message 8121, Level 16, State 1, Line 1
Column 'INFO.id' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.

消息 8121，级别 16，状态 1，第 1
行列“INFO.id”在 HAVING 子句中无效，因为它未包含在聚合函数或 GROUP BY 子句中。

What he wanted actually was the table INFO max value of ID. Based on the problem statement what the right solution is as following, which does not generate an error.

他真正想要的是ID的表INFO最大值。根据问题陈述，正确的解决方案如下，不会产生错误。

SELECT * FROM INFO WHERE ID = (SELECT MAX(ID) FROM INFO)

ScreenShot

截屏

回答by max68

I think the cleanest solution is to use the ALL comparison condition. It is used to compare a value to a list or subquery.

我认为最干净的解决方案是使用 ALL 比较条件。它用于将值与列表或子查询进行比较。

SELECT 
  m.mem_desc,
  m.mem_max_rentals,
  mh.mem_type,      
  COUNT(mh.mem_type) as membership_count
FROM membership_history mh
JOIN membership m ON m.mem_type = mh.mem_type
GROUP BY mh.mem_type,m.mem_desc,m.mem_max_rentals
HAVING membership_count >= ALL (
  SELECT count(*)
  FROM membership_history
  GROUP BY mem_type
)   

回答by Aspirant

You cannot use a derived table directly in where clause it will give table or view does not existerror so to get the max count value you can use HAVINGclause or Analytical Functionsor Rownumlike

您不能直接在 where 子句中使用派生表，它会table or view does not exist出错，因此要获得可以使用HAVING子句Analytical Functions或Rownum类似的最大计数值

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 a
      JOIN membership b ON b.mem_type = a.mem_type
      group by (membership_history.mem_type,membership.mem_desc,membership.mem_max_rentals)
      having count(a.MEM_TYPE) = (Select      
      MAX(count(a.MEM_TYPE)) from membership_history a
      JOIN membership b ON b.mem_type = a.mem_type
      group by (a.mem_type,b.mem_desc,b.mem_max_rentals)));

(OR)

（或者）

select * from
(SELECT g.*,rank() over (order by membership_count desc) rnk 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 rnk=1;

(OR)

（或者）

select * from
(SELECT g.*,rownum rn 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 membership_count desc) g)
WHERE rn=1;

回答by Scotch

You could try something like

你可以尝试类似的东西

 SELECT membership.mem_desc,membership.mem_max_rentals,membership_history.mem_type, membership_count, rank() over ORDER BY membership_count DESC as ranky
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)
) 
WHERE ranky =1;