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

问题描述

我有一个表，其中的数据如下所示:

数据类型，值魔兽世界，500地震 3, 1500地震 3, 1400魔兽世界，1200最终幻想，100最终幻想，500

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

选择数据类型，最大值(值)从表其中 data_type = [在此处插入每种数据类型以进行单独的查询]按数据类型分组

但我希望它显示的是

选择数据类型，最大值(值)为魔兽世界"，最大值(值)为地震 3"，最大值(值)作为最终幻想"

所以我在一个语句中得到了每个的最大值.我该怎么做呢?

解决方案

如果你想在单独的列中返回每个 data_type 的最大值，那么你应该能够使用带有 CASE 表达式的聚合函数:

p>

选择max(case when data_type='World of Warcraft' then value end) WorldofWarcraft,最大值(当 data_type='Quake 3' 然后值结束时的情况) Quake3，最大值(当 data_type='Final Fantasy' 然后值结束时的情况)FinalFantasy从你的桌子上；

参见 SQL Fiddle with Demo

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

But what I want it to display is is

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?

解决方案

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;

See SQL Fiddle with Demo

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