SELECT id FROM ( SELECT @rownum:=@rownum+1 AS rownum,# 行号 IF(@x=uo.apply_id,@rank:=@rank+1,@rank:=1) rank,#处理排名,如果@x等于user_id,则表示@x被初始化,将@rank自增1 @x:=uo.apply_id, # 初始化@x,@x为中间变量,在rank之后初始化,所以,rank初始化时,@x为null或者是上一个user_id的值 id,apply_id FROM cpap_dispense uo, (SELECT @rownum:=0,@rank:=0) init # 初始化信息表 ORDER BY `apply_id`,suggesttime asc)result where rank=1CREATE
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实现分组排序测试表'
;
INSERT
INTO
`user_orders` (`orders_id`, `user_id`, `add_time`)
VALUES
(
'1'
,
'1'
,
'1'
);
INSERT
INTO
`user_orders` (`orders_id`, `user_id`, `add_time`)
VALUES
(
'2'
,
'1'
,
'2'
);
INSERT
INTO
`user_orders` (`orders_id`, `user_id`, `add_time`)
VALUES
(
'3'
,
'1'
,
'3'
);
INSERT
INTO
`user_orders` (`orders_id`, `user_id`, `add_time`)
VALUES
(
'4'
,
'2'
,
'1'
);
INSERT
INTO
`user_orders` (`orders_id`, `user_id`, `add_time`)
VALUES
(
'5'
,
'2'
,
'2'
);
INSERT
INTO
`user_orders` (`orders_id`, `user_id`, `add_time`)
VALUES
(
'6'
,
'2'
,
'3'
);
INSERT
INTO
`user_orders` (`orders_id`, `user_id`, `add_time`)
VALUES
(
'7'
,
'3'
,
'1'
);
INSERT
INTO
`user_orders` (`orders_id`, `user_id`, `add_time`)
VALUES
(
'8'
,
'3'
,
'2'
);
INSERT
INTO
`user_orders` (`orders_id`, `user_id`, `add_time`)
VALUES
(
'9'
,
'3'
,
'3'
);
SELECT
orders_id,user_id,add_time,rank
FROM
(
SELECT
@rownum:=@rownum+1
AS
rownum,# 行号
IF(@x=uo.user_id,@rank:=@rank+1,@rank:=1) rank,#处理排名,如果@x等于user_id,则表示@x被初始化,将@rank自增1
@x:=uo.user_id, # 初始化@x,@x为中间变量,在rank之后初始化,所以,rank初始化时,@x为
null
或者是上一个user_id的值
orders_id,user_id,add_time
FROM
user_orders uo,
(
SELECT
@rownum:=0,@rank:=0) init # 初始化信息表
ORDER
BY
user_id
ASC
, add_time
DESC
)result
WHERE
rank=2
提取所有用户的第二个订单信息
最新推荐文章于 2023-03-01 21:35:29 发布