MySQL implementation grouping sorting

To extract the second order information for all users based on the user group, with the order of the user descending.
This is a sort of grouping, where Oracle has built-in functions that can be implemented, while MySQL is a bit of a hassle:
CREATE TABLE user_orders (orders_id INT UNSIGNED not NULL,
user_id INT UNSIGNED not NULL,
Add_time INT UNSIGNED not NULL,
PRIMARY KEY (orders_id),
KEY (user_id),
KEY (Add_time)
Engine=innodb DEFAULT CHARSET UTF8 COMMENT ' MySQL Implementation Group sort test table ';

INSERT intouser_orders(orders_id,user_id,add_timeVALUES (' 1 ', ' 1 ', ' 1 ');
INSERT intouser_orders(orders_id,user_id,add_timeVALUES (' 2 ', ' 1 ', ' 2 ');
INSERT intouser_orders(orders_id,user_id,add_timeVALUES (' 3 ', ' 1 ', ' 3 ');
INSERT intouser_orders(orders_id,user_id,add_timeVALUES (' 4 ', ' 2 ', ' 1 ');
INSERT intouser_orders(orders_id,user_id,add_timeVALUES (' 5 ', ' 2 ', ' 2 ');
INSERT intouser_orders(orders_id,user_id,add_timeVALUES (' 6 ', ' 2 ', ' 3 ');
INSERT intouser_orders(orders_id,user_id,add_timeVALUES (' 7 ', ' 3 ', ' 1 ');
INSERT intouser_orders(orders_id,user_id,add_timeVALUES (' 8 ', ' 3 ', ' 2 ');
INSERT intouser_orders(orders_id,user_id,add_timeVALUES (' 9 ', ' 3 ', ' 3 ');

SELECT Orders_id,user_id,add_time,rank from (
SELECT @rownum:[email protected]+1 as rownum,# line number
if (@x=uo.user_id, @rank:[email protected]+1, @rank: =1) rank, #处理排名, if @x equals user_id, then @x is initialized, @rank is increased by 1
@x:=uo.user_id, # Initializes @x,@x as an intermediate variable, initializes after rank, so that when rank is initialized, @x is null or the value of the previous user_id
Orders_id,user_id,add_time
From
User_orders UO,
(SELECT @rownum: =0, @rank: =0) INIT # Initialization Information table
ORDER by user_id ASC, Add_time DESC
) result
WHERE rank=2

Focus: The key is how @x is assigned. Once you understand @x's assignment, you can immediately understand the origin of rank.

MySQL implementation grouping sorting