我有一个表,其中的数据如下所示:
数据类型,值魔兽世界,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
上一篇:无法打开,从布局和放两个main.xml中的文件;布局土地在同一时间?布局、无法打开、土地、两个
下一篇:onListItemClick()不工作的ListView在Android的工作、onListItemClick、Android、ListView