我正在获取按组最大值(data
CTE 模拟了从实际表中进行的一组连接):
with data as (
select 'Austria' as country, 1 as id, 'red' as colour, 120 as quantity
union all select 'Austria', 2, 'green', 96
union all select 'Austria', 3, 'blue', 103
union all select 'Belgium', 1, 'red', 33
union all select 'Belgium', 2, 'green', 12
union all select 'Belgium', 3, 'blue', 40
)
select country, colour, quantity
from (
select country, colour, quantity,
rank() over (partition by country order by quantity desc, id) as position
from data
group by country, id, colour, quantity
) subquery
where position=1;
这样做是有效的,但我被迫将查询用RANK()
包装在子查询中,因为这种替代方法会触发语法错误:
-- [...]
select country, colour, quantity,
rank() over (partition by country order by quantity desc, id) as position
from data
group by country, id, colour, quantity
having rank() over (partition by country order by quantity desc, id)=1;
窗口函数只能出现在SELECT或ORDER BY子句中。
是否有替代语法来避免这个限制,或者子查询是唯一合理的方法?
最终目标是将此代码集成到更大的动态生成的SQL表达式集中。如果我可以保持单个查询,我可以用数组定义不同的部分(选择、连接的表、where、group by、having和order by)。否则我需要考虑进行重大改写。
GROUP BY
子句是代码编辑的剩余部分。 - Álvaro González