数据库里边有表
t(time, loction, index)
数据形式为
2009-01-01 01:00:00 1 10
2009-01-01 01:00:00 2 3
2009-01-01 01:00:00 3 1
2009-01-01 01:00:00 4 101
2009-01-01 01:00:00 5 102
2009-01-02 01:00:00 1 3
2009-01-02 01:00:00 2 3
2009-01-02 01:00:00 3 5
2009-01-02 01:00:00 4 10
2009-01-02 01:00:00 5 18
我需要的结果是根据时间分组topn例如我现在需要 时间分组top max 2的结果如下2009-01-01 01:00:00 5 102
2009-01-01 01:00:00 4 101
2009-01-02 01:00:00 5 18
2009-01-02 01:00:00 4 10请问各位大侠有什么sql可以达到这样的结果吗
t(time, loction, index)
数据形式为
2009-01-01 01:00:00 1 10
2009-01-01 01:00:00 2 3
2009-01-01 01:00:00 3 1
2009-01-01 01:00:00 4 101
2009-01-01 01:00:00 5 102
2009-01-02 01:00:00 1 3
2009-01-02 01:00:00 2 3
2009-01-02 01:00:00 3 5
2009-01-02 01:00:00 4 10
2009-01-02 01:00:00 5 18
我需要的结果是根据时间分组topn例如我现在需要 时间分组top max 2的结果如下2009-01-01 01:00:00 5 102
2009-01-01 01:00:00 4 101
2009-01-02 01:00:00 5 18
2009-01-02 01:00:00 4 10请问各位大侠有什么sql可以达到这样的结果吗
(
select t.*,row_number() over(parition by time order by index desc,loction) rn
from table1 t
)a
where rn<3
select time, loction, index from(
select t.*,dense_rank()over(partition by time order by loction desc)dk
from t)
where dk<=2
用这个把如果是取最大的两条如果是可以并列的,把row_number换成rank注意他们的用户和区别