在我的表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
、payments_2
、payments_3
:
1payments
->一切都好!
2payments_2
->一切都好!用户尚未付款,因此应抓取第一行
3payments_3
数据不正确。用户已经付款,因此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');
用@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()