有一个表membergreen,结构三个字段:greenid,cardno,greennum
表中有数据,每个cardno对应有三条数据
GREENID CARDNO GREENNUM
120542 5000000043350 0
143912 5000000043350 41
143913 5000000043350 48
144708 5000000043351 42
144709 5000000043351 48
120543 5000000043351 0
我想按cardno字段group by,然后再按greenid排序,前greenid较小的前两条数据,怎样写这个语句?
得出的结果要是:
GREENID CARDNO GREENNUM
120542 5000000043350 0
143912 5000000043350 41120543 5000000043351 0
144708 5000000043351 42
表中有数据,每个cardno对应有三条数据
GREENID CARDNO GREENNUM
120542 5000000043350 0
143912 5000000043350 41
143913 5000000043350 48
144708 5000000043351 42
144709 5000000043351 48
120543 5000000043351 0
我想按cardno字段group by,然后再按greenid排序,前greenid较小的前两条数据,怎样写这个语句?
得出的结果要是:
GREENID CARDNO GREENNUM
120542 5000000043350 0
143912 5000000043350 41120543 5000000043351 0
144708 5000000043351 42
select * from (select greenid,cardno,greennum,row_number() over(partition by cardno order by GREENID) rn) where rn<=2;
from (select greenid,
cardno,
greennum,
row_number() over(partition by cardno order by GREENID) rn
from membergreen)
where rn <= 2;
from (
select greenid,cardno,greennum,row_number() over(partition by cardno order by GREENID) rn
from membergreen
) where rn<=2;