SQL:对每个不同的C都使用max B。

In my example, I have a table containing info about different venues, with columns for city, venue_name, and capacity. I need to select the city and venue_name for the venue with the highest capacity within each city. So if I have data:

在我的示例中，我有一个包含不同场所信息的表，其中包含city、venue_name和容量的列。我需要选择城市和venue_name的地点，在每个城市的最高容量。如果我有数据

city | venue  | capacity
LA   | venue1 | 10000
LA   | venue2 | 20000 
NY   | venue3 | 1000
NY   | venue4 | 500

... the query should return:

…查询应该返回:

LA | venue2 
NY | venue3

Can anybody give me advice on how to accomplish this query in SQL? I've gotten tangled up in joins and nested queries :P. Thanks!

关于如何用SQL完成这个查询，有人能给我一些建议吗?我在连接和嵌套查询中纠结:P。谢谢!

4 个解决方案

#1

select t.city, t.venue
  from tbl t
  join (select city, max(capacity) as max_capacity from tbl group by city) v
    on t.city = v.city
   and t.capacity = v.max_capacity

#2

One way to do this is with not exists:

一种方法是不存在:

select i.*
from info i
where not exists (select 1
                  from into i2
                  where i2.city = i.city and i2.capacity > i.capacity);

#3

The common approach is to join the table back to itself using a subquery with max:

常用的方法是使用max:

select y.city, y.venue_name
from yourtable y
    join (select city, max(capacity) maxcapacity
          from yourtable
          group by city
    ) t on y.city = t.city and y.capacity = t.maxcapacity

#4

You can use an outer apply to order those values and bring the results back to your main query.

您可以使用外部应用程序来排序这些值，并将结果返回到主查询。

http://www.codeproject.com/Articles/607246/Making-OUTER-and-CROSS-APPLY-work-for-you

Another alternative would be to use the RowNum() function. http://msdn.microsoft.com/en-us/library/ms186734.aspx

另一种选择是使用RowNum()函数。http://msdn.microsoft.com/en-us/library/ms186734.aspx

SELECT 
  v.city, 
  Ranked.Venue,
  Ranked.Capacity   
FROM Venues v WITH (NOLOCK) 
   Outer Apply 
        ( 
           SELECT TOP 1 
                Venue, Capacity
            FROM Venues Ranked WITH (NOLOCK) 
            WHERE v.City = Ranked.City 
            ORDER BY Capacity DESC 
         ) as Ranked 
 GROUP BY 
     v.city,
     Ranked.Venue,
     Ranked.Capacity

#1

select t.city, t.venue
  from tbl t
  join (select city, max(capacity) as max_capacity from tbl group by city) v
    on t.city = v.city
   and t.capacity = v.max_capacity

#2