数据库表A字段有 id，仓库号storeId，日期date，品种product，规格spec，金额money
查询每一个仓库每一日期金额前5名的数据（sql或存储过程）
如下所示
仓库号 日期 品种 规格 金额 排名
5 2009-8 A A*B 50000 1
5 2009-8 C A*G 40000 2
5 2009-8 H S*B 30000 3
5 2009-8 Q D*K 20000 4
5 2009-8 B A*B*C 10000 5
5 2009-11 A A*B 50000 1
5 2009-11 C A*G 40000 2
5 2009-11 H S*B 30000 3
5 2009-11 Q D*K 20000 4
5 2009-11 B A*B*C 10000 5
8 2009-8 K S*B 40000 1
8 2009-8 C J*G 35000 2
8 2009-8 T S*Q 30000 3
8 2009-8 Q D*K*H 15000 4
8 2009-8 W A*B*C 10000 5
8 2009-11 K S*B 40000 1
8 2009-11 C J*G 35000 2
8 2009-11 T S*Q 30000 3
8 2009-11 Q D*K*H 15000 4
8 2009-11 W A*B*C 10000 5
[code="sql"]select * from (
select id,storeid,date,product,spec,
money,row_number() over(partition by money desc order by storeid,date) rn
from table
) where rn <= 5[/code]
select * from table_name where id in
(
select top id from table_name group by storeId,date order by money desc
)as t
试试~
[code="java"]
select * from table_name where id in
(
select top id from table_name group by storeId,date order by money desc
)as t
[/code]
[quote]
select * from table_name where id in
(
select top id from table_name group by storeId,date order by money desc
)as t
[/quote]
搞错了 呵呵~
分组是group by 啊
group by 是通用的
[quote]row_number() over()是分组排序函数吗？各个数据库通用吗
[/quote]
是oracle特性函数，不能通用。优点是语法简单，易于理解。