php - MySQL查询以获取最后N次失败的付款

Question

我的数据库有两个表:users 和 payments。 users 和payments 之间存在一对多的关系:每个用户可以有 o 个或多个 payments，一个 payment 属于一个用户。此外，每次付款都可以成功或失败。

我需要编写一个查询来获取恰好在最后 N 次付款中失败的所有用户。

我找到了这个查询，它允许所有支付了 N 次或更多次(在本例中为 4 次或更多)的用户:

SELECT x.user_id, count(*) as cnt 
FROM (
    SELECT a.user_id, a.date, a.status FROM payment AS a WHERE 
        (SELECT COUNT(*) FROM payment AS b 
         WHERE b.user_id = a.user_id AND b.date >= a.date) <= 4 
         ORDER BY a.user_id ASC, a.date DESC) AS x 
WHERE x.status = 'failed' 
GROUP BY x.user_id
HAVING cnt >=4;

但我不能让它对一个确切的数字起作用(在这个例子中，恰好是 4)。

表的结构是:

• 用户:id、姓名、电子邮件、密码、created_at、updated_at
• payments: id, date (他们付款的日期), status (success, failed), user_id, created_at, updated_at

一个例子:

这sqlfiddle可能有助于了解我需要什么。它应该只返回用户 4(最近 4 次付款失败的用户，但也返回用户 5(有 5 次失败的付款)。

相同的 DDL:

CREATE TABLE users
    (`id` int, `name` varchar(6), `email` varchar(7), `password` varchar(10), `created_at` timestamp, `updated_at` timestamp)
;

INSERT INTO users
    (`id`, `name`, `email`, `password`)
VALUES
    (1, 'name 1', 'email 1', 'password 1'),
    (2, 'name 2', 'email 2', 'password 2'),
    (3, 'name 3', 'email 3', 'password 3'),
    (4, 'name 4', 'email 4', 'password 4'),
    (5, 'name 5', 'email 5', 'password 5')
;


CREATE TABLE payments
    (`id` int, `date` varchar(10), `status` varchar(7), `user_id` int ,`created_at` timestamp, `updated_at` timestamp)
;

INSERT INTO payments
    (`id`, `date`, `status`, `user_id`)
VALUES
    (1, '2019-01-01', 'success', 1),
    (2, '2019-01-01', 'failed', 2),
    (3, '2019-01-01', 'failed', 3),
    (4, '2019-01-01', 'success', 4),
    (5, '2019-01-01', 'success', 5),
    (6, '2019-01-02', 'success', 1),
    (7, '2019-01-02', 'success', 2),
    (8, '2019-01-02', 'success', 3),
    (9, '2019-01-02', 'success', 4),
    (10, '2019-01-02', 'success', 5),
    (11, '2019-01-03', 'success', 1),
    (12, '2019-01-03', 'failed', 2),
    (13, '2019-01-03', 'success', 3),
    (14, '2019-01-03', 'failed', 4),
    (15, '2019-01-03', 'failed', 5),
    (16, '2019-01-04', 'success', 1),
    (17, '2019-01-04', 'failed', 2),
    (18, '2019-01-04', 'failed', 3),
    (19, '2019-01-04', 'failed', 4),
    (20, '2019-01-04', 'failed', 5),
    (21, '2019-01-05', 'success', 1),
    (22, '2019-01-05', 'failed', 2),
    (23, '2019-01-05', 'failed', 3),
    (24, '2019-01-05', 'failed', 4),
    (25, '2019-01-05', 'failed', 5),
    (26, '2019-01-06', 'success', 1),
    (27, '2019-01-06', 'success', 2),
    (28, '2019-01-06', 'failed', 3),
    (29, '2019-01-06', 'failed', 4),
    (30, '2019-01-06', 'failed', 5),
    (31, '2019-01-07', 'failed', 5)
;

Answer 1

我已经用更简单的 SQL 语句更新了您的 SQL Fiddle，它产生了正确的结果。如果您只需要有特定失败付款次数的用户，请将 >= 更改为 =。

SQL Fiddle

MySQL 5.6 架构设置:

CREATE TABLE users
    (`id` int, `name` varchar(6), `email` varchar(7), `password` varchar(10), `created_at` timestamp, `updated_at` timestamp)
;

INSERT INTO users
    (`id`, `name`, `email`, `password`)
VALUES
    (1, 'name 1', 'email 1', 'password 1'),
    (2, 'name 2', 'email 2', 'password 2'),
    (3, 'name 3', 'email 3', 'password 3'),
    (4, 'name 4', 'email 4', 'password 4'),
    (5, 'name 5', 'email 5', 'password 5')
;


CREATE TABLE payments
    (`id` int, `date` varchar(10), `status` varchar(7), `user_id` int ,`created_at` timestamp, `updated_at` timestamp)
;

INSERT INTO payments
    (`id`, `date`, `status`, `user_id`)
VALUES
    (1, '2019-01-01', 'success', 1),
    (2, '2019-01-01', 'failed', 2),
    (3, '2019-01-01', 'failed', 3),
    (4, '2019-01-01', 'success', 4),
    (5, '2019-01-01', 'success', 5),
    (6, '2019-01-02', 'success', 1),
    (7, '2019-01-02', 'success', 2),
    (8, '2019-01-02', 'success', 3),
    (9, '2019-01-02', 'success', 4),
    (10, '2019-01-02', 'success', 5),
    (11, '2019-01-03', 'success', 1),
    (12, '2019-01-03', 'failed', 2),
    (13, '2019-01-03', 'success', 3),
    (14, '2019-01-03', 'failed', 4),
    (15, '2019-01-03', 'failed', 5),
    (16, '2019-01-04', 'success', 1),
    (17, '2019-01-04', 'failed', 2),
    (18, '2019-01-04', 'failed', 3),
    (19, '2019-01-04', 'failed', 4),
    (20, '2019-01-04', 'failed', 5),
    (21, '2019-01-05', 'success', 1),
    (22, '2019-01-05', 'failed', 2),
    (23, '2019-01-05', 'failed', 3),
    (24, '2019-01-05', 'failed', 4),
    (25, '2019-01-05', 'failed', 5),
    (26, '2019-01-06', 'success', 1),
    (27, '2019-01-06', 'success', 2),
    (28, '2019-01-06', 'failed', 3),
    (29, '2019-01-06', 'failed', 4),
    (30, '2019-01-06', 'failed', 5),
    (31, '2019-01-07', 'failed', 5)
;

查询 1:

SELECT x.user_id, count(*) as cnt 
FROM (
    SELECT a.user_id, a.date, a.status FROM payments AS a WHERE 
        (SELECT COUNT(*) FROM payments AS b 
         WHERE b.user_id = a.user_id AND b.date >= a.date) <= 4 
         ORDER BY a.user_id ASC, a.date DESC) AS x 
WHERE x.status = 'failed' 
GROUP BY x.user_id
HAVING cnt >=4

Results :

| user_id | cnt |
|---------|-----|
|       4 |   4 |
|       5 |   4 |

查询 2:

SELECT `user_id`, count(*) as `cnt`
FROM `payments` 
WHERE `status` = 'failed'
GROUP BY `user_id`
HAVING cnt >= 4
ORDER BY `cnt`

Results :

| user_id | cnt |
|---------|-----|
|       2 |   4 |
|       3 |   4 |
|       4 |   4 |
|       5 |   5 |