MySQL查找上次付款是否过期

Question

在我的表payments中，我处理用户已经进行的两次付款&；审判。试用设置为付费，但总额为零（0.00美元），如:

ID | Subscription | Plan | Total | Paid | Paid at | Expires at
1    1              1      0.00    1      null      2020-11-05

当试用版即将过期时，强制用户购买计划，将创建以下行:

ID | Subscription | Plan | Total | Paid | Paid at     | Expires at
1    1              1      0.00    1      null          2020-11-05
2    1              1      15.00   1      2020-11-06    2020-12-06

如您所见，total不再为零，变量paid_at也被填充。SQL代码应该读取行号2。

我现在需要的是核实上次付款是否过期。但在我们继续之前，请记住以下几点:用户可能选择购买一个计划，但不立即付款，这意味着第一笔付款必须是考虑的那一笔。示例:

ID | Subscription | Plan | Total | Paid | Paid at     | Expires at
1    1              1      0.00    1      null          2020-11-05
2    1              1      15.00   0      null          2020-12-06

在这种情况下，用户决定购买该计划，但尚未付款。SQL逻辑应该读取行号1的expires_at。

当前我的SQL代码如下:

SELECT * 
FROM payments 
WHERE expires_at < '2020-11-06'
AND paid = true 
and subscription_id = 1
ORDER BY id DESC
LIMIT 1

我知道这永远不会起作用，因为它总是获取行号1，无论我是否有更多的行。我也知道，我可以选择最后一行支付，然后通过代码验证它是否已经过期，但我想通过SQL做这件事。

编辑1:SQL Fiddle帮助

我创建了三个表payments，payments2，payments3:

lt；1gt；付款->；一切正常！

2付款2->；一切正常！用户还没有付费，所以应该抓取第一行

3Payments3数据不正确。用户已经付款，因此SQL应该验证最后的expires_at行

CREATE TABLE `payments` (
  `id` int(11) NOT NULL,
  `subscription_id` int(11) NOT NULL,
  `plan_id` int(11) NOT NULL,
  `total` double(10,2) NOT NULL,
  `expires_at` date NOT NULL,
  `paid` tinyint(1) NOT NULL,
  `paid_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
ALTER TABLE `payments` ADD PRIMARY KEY (`id`);

INSERT INTO payments VALUES(1, 1, 1, 0, '2020-11-05', 1, null);

CREATE TABLE `payments_2` (
  `id` int(11) NOT NULL,
  `subscription_id` int(11) NOT NULL,
  `plan_id` int(11) NOT NULL,
  `total` double(10,2) NOT NULL,
  `expires_at` date NOT NULL,
  `paid` tinyint(1) NOT NULL,
  `paid_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
ALTER TABLE `payments_2` ADD PRIMARY KEY (`id`);

INSERT INTO payments_2 VALUES(1, 1, 1, 0, '2020-11-05', 1, null);
INSERT INTO payments_2 VALUES(2, 1, 1, 15.00, '2020-12-06', 1, null);

CREATE TABLE `payments_3` (
  `id` int(11) NOT NULL,
  `subscription_id` int(11) NOT NULL,
  `plan_id` int(11) NOT NULL,
  `total` double(10,2) NOT NULL,
  `expires_at` date NOT NULL,
  `paid` tinyint(1) NOT NULL,
  `paid_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
ALTER TABLE `payments_3` ADD PRIMARY KEY (`id`);

INSERT INTO payments_3 VALUES(1, 1, 1, 0, '2020-11-05', 1, null);
INSERT INTO payments_3 VALUES(2, 1, 1, 15.00, '2020-12-06', 1, '2020-11-06 15:00:00');

Answer 1

用@NBK的提示解决。在MySQL中，我们不能在where中使用MAX，因此:

SELECT *
FROM `payments`
where subscription_id = 1 
  and (SELECT MAX(expires_at) 
         FROM payments 
        WHERE subscription_id = 1 AND paid = true) < CURDATE()