My SQL和LINQ 实现ROW_NUMBER() OVER
标签：
My SQL和LINQ 实现ROW_NUMBER() OVER
exists Wmy;
create table Wmy (id int ,GroupId int ,salary decimal(10,2) );
insert into Wmy values
(1,10,5500.00),
(2,10,4500.00),
(3,20,1900.00),
(4,20,4800.00),
(5,40,6500.00),
(6,40,14500.00),
(7,40,44500.00),
(8,50,6500.00),
(9,50,7500.00);
select id,GroupId,salary,rank from
(
select H.id,H.GroupId,H.salary,@rownum:=@rownum+1 ,
if(@Group=H.GroupId,@rank:=@rank+1,@rank:=1) as rank,
@Group:=H.GroupId
from (
select id,GroupId,salary from Wmy order by GroupId asc ,salary desc
) H ,(select @rownum :=0 , @Group := null ,@rank:=0) a ) result;