使用rank函数优化sql查询
此查询使用排名函数获取每组中的顶部项目。
我想将内部选择的数量减少到两个而不是三个。我尝试在最里面的查询中使用rank()函数，但无法让它与聚合函数一起工作。然后，如果不将“itemrank”包装在另一个 select 语句中，我就无法在“itemrank”上使用 where 子句。
有什么想法吗？
select *
from (
select
tmp.*,
rank() over (partition by tmp.slot order by slot, itemcount desc) as itemrank
from (
select
i.name,
i.icon,
ci.slot,
count(i.itemid) as itemcount
from items i
inner join citems ci on ci.itemid = i.itemid
group by i.name, i.icon, ci.slot
) as tmp
) as popularitems
where itemrank = 1
编辑：使用sql server 2008
This query gets the top item in each group using the ranking function.
I want to reduce the number of inner selects down to two instead of three. I tried using the rank() function in the innermost query, but couldn't get it working along with an aggregate function. Then I couldn't use a where clause on 'itemrank' without wrapping it in yet another select statement.
Any ideas?
select *
from (
select
tmp.*,
rank() over (partition by tmp.slot order by slot, itemcount desc) as itemrank
from (
select
i.name,
i.icon,
ci.slot,
count(i.itemid) as itemcount
from items i
inner join citems ci on ci.itemid = i.itemid
group by i.name, i.icon, ci.slot
) as tmp
) as popularitems
where itemrank = 1
EDIT: using sql server 2008
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
在 Oracle 和 Teradata（也许还有其他）中，您可以使用 QUALIFY itemrank = 1 来摆脱外部选择。这不是 ANSI 标准的一部分。
In Oracle and Teradata (and perhaps others too), you can use
QUALIFY itemrank = 1to get rid of the outer select. This is not part of the ANSI standard.您可以在 Oracle 或 SQL Server 中使用公用表表达式。
语法如下：
仅当查询定义中提供了所有结果列的不同名称时，列名称列表才是可选的。
运行CTE的语句是：
You can use Common Table Expressions in Oracle or in SQL Server.
Here is the syntax:
The list of column names is optional only if distinct names for all resulting columns are supplied in the query definition.
The statement to run the CTE is: