使用单个SQL语句选择多个max()值 [英] Selecting multiple max() values using a single SQL statement

问题描述

我有一个表，其中的数据看起来像这样:

I have a table that has data that looks something like this:

data_type, value
World of Warcraft, 500
Quake 3, 1500
Quake 3, 1400
World of Warcraft, 1200
Final Fantasy, 100
Final Fantasy, 500

我想做的是在单个语句中选择每个值的最大值.我知道我可以轻松地做类似的事情

What I want to do is select the maximum of each of these values in a single statement. I know I can easily do something like

select data_type, max(value)
from table
where data_type = [insert each data type here for separate queries]
group by data_type

但是我想要显示的是

select data_type, 
  max(value) as 'World of Warcraft', 
  max(value) as 'Quake 3', 
  max(value) as 'Final Fantasy'

因此，我在单个语句中获得了每一个的最大值.我将如何去做?

So I get the max value of each of these in a single statement. How would I go about doing this?

推荐答案

如果要在单独的列中返回每个data_type的最大值，则应该能够将聚合函数与CASE表达式一起使用:

If you want to return the max value for each data_type in a separate column, then you should be able to use an aggregate function with a CASE expression:

select
  max(case when data_type='World of Warcraft' then value end) WorldofWarcraft,
  max(case when data_type='Quake 3' then value end) Quake3,
  max(case when data_type='Final Fantasy' then value end) FinalFantasy
from yourtable;

请参见带有演示的SQL提琴

这篇关于使用单个SQL语句选择多个max()值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！