mysql - SQL : ORDER BY ASC with LIMIT

Question

我对 MySQL 数据库的 SQL 查询:

SELECT `id`, `id_sender`, `id_recipient`, `date_time`, `amount_money`, `id_currency`, `transfer_title`, `authorization_key`, `authorization_status`
FROM `bankapplication_transactions` AS `transaction`
WHERE ((`transaction`.`date_time`
BETWEEN '2019-03-21 08:56:22' AND '2019-04-21 07:56:22'
AND `transaction`.`authorization_status` = 1)
AND (`transaction`.`id_sender` = 3 OR `transaction`.`id_recipient` = 3))
ORDER BY `transaction`.`date_time` ASC

我的结果:

id | id_sender | id_recipient | date_time           | ...
2  | 1         | 3            | 2019-04-21 07:51:25 | ...
4  | 4         | 3            | 2019-04-21 07:52:38 | ...
5  | 3         | 4            | 2019-04-21 07:54:39 | ...

我想限制为 2，他会返回最后两个结果，但让它继续用 ASC 操作，所以我需要:

id | id_sender | id_recipient | date_time           | ...
4  | 4         | 3            | 2019-04-21 07:52:38 | ...
5  | 3         | 4            | 2019-04-21 07:54:39 | ...

我为 NodeJS 使用 Sequelize ORM，所以这是我的 Controller :

Transaction.findAll({
      where: db.Sequelize.and(
        {
          date_time: {
            [Op.between]: [
              getPreviousMonthDate(getTodayDate()),
              getTodayDate(),
            ],
          },
          authorization_status: setAuthorizationStatus(1),
        },
        db.Sequelize.or({ id_sender: userId }, { id_recipient: userId }),
      ),
      order: [['date_time', 'ASC']],
    })

Answer 1

您需要按 desc 订购并限制为 2

SELECT `id`, `id_sender`, `id_recipient`, `date_time`, `amount_money`, `id_currency`, `transfer_title`, `authorization_key`, `authorization_status`
FROM `bankapplication_transactions` AS `transaction`
WHERE ((`transaction`.`date_time`
BETWEEN '2019-03-21 08:56:22' AND '2019-04-21 07:56:22'
AND `transaction`.`authorization_status` = 1)
AND (`transaction`.`id_sender` = 3 OR `transaction`.`id_recipient` = 3))
ORDER BY `transaction`.`date_time` desc
lmit 2