Db2 - 返回每个类别的前 5 个 [英] Db2 - Returning the top 5 of each category

问题描述

我希望每个菜单能够返回 5 个菜单项.这是表格

<上一页>菜单--------菜单项 int()个人资料名称 varchar(35)菜单项------------itemid int()名称 varchar(40)菜单项 int()

我确实在这个线程中看到了 MySQL 的解决方案 - mySQL Returning每个类别的前 5 名，正在为 DB2 寻找类似的解决方案.任何建议都非常感谢.

解决方案

类似:

选择...从 (select ..., row_number() over ( 按 m.menuid 分区订购?) 作为 rn从菜单 m加入菜单项 mim.menuid = mi.menuid)其中 rn <= 5;

Troels Arvin 对不同 DBMS 进行了比较:

http://troels.arvin.dk/db/rdbms/

除其他事项外，还讨论了配额查询(例如您要查询的查询)

I want to be able to return 5 menuitem per menu. Here are the tables

menus
-------
menuid int()
profileName varchar(35)

menuitems
-----------
itemid int()
name varchar(40)
menuid int()

I do see a solution for MySQL in this thread - mySQL Returning the top 5 of each category, looking for similar solution for DB2. Any suggestion is great appreciated.

解决方案

something like:

select ...
from (
    select ..., row_number() over ( partition by m.menuid
                                    order by ? ) as rn 
    from menus m
    join menuitems mi
        m.menuid = mi.menuid
)
where rn <= 5;

Troels Arvin has a comparision of different DBMS at:

http://troels.arvin.dk/db/rdbms/

Amongst other things quota queries (such as the one you are asking) are discussed

这篇关于Db2 - 返回每个类别的前 5 个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！