select type,max(age) from A group by type 不会有错
select type,max(age),name from A group by type 有错,不是group by 表达式,因为select 了name的值
我这里不需要group by name,但我有想通过一个sql把name也select出来。希望结果
select type,max(age),name from A group by type 有错,不是group by 表达式,因为select 了name的值
我这里不需要group by name,但我有想通过一个sql把name也select出来。希望结果
select type,max(age),name
from A
group by type,name
with t1 as(
select '人' type,19 age,'张三' name from dual
union all
select '人' type,10 age,'李四' name from dual
union all
select '人' type,8 age,'王五' name from dual
union all
select '猫' type,7 age,'小花' name from dual
union all
select '猫' type,5 age,'小黑' name from dual
union all
select '狗' type,9 age,'小狗' name from dual
union all
select '狗' type,5 age,'大狗' name from dual
)select type,max(age) age,max(name) name
from t1
group by type
type age name
--------------------------------------
1 狗 9 小狗
2 猫 7 小花
3 人 19 张三
我说了,不要group by name,仅仅group by type,因为group by name 会查出更多的数据。
from
(
select type,age,name,row_number() over(partition by type order by age desc) rn
from t1
)
where rn = 1
这函数只是针对单行的值 分组取最大值赋给单行 在这里肯定达不到
至少LZ这要求 还真不知道有方法可以完成的
第一种:
select * from aa where type||age in (
select type||max(age) from aa group by type)
第二种:
select type, age, name
from (select aa.*, rank() over(partition by type order by age desc) rank
from aa)
where rank = 1;