想要根据用户分组,以该用户的下单时间为降序,提取所有用户的第二个订单信息。
这属于分组排序,在Oracle有内置函数可以实现,而在mysql,做起来比较蛋疼。
我提供一种方法供参考
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实现分组排序测试表'
;
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
重点:关键在于@x如何赋值。了解@x的赋值之后,立马就能明白rank(名次)的由来。