Table payment_transaction
+----+---------+--------+------------+
| ID | user_id | status | time_stamp |
+----+---------+--------+------------+
| 1 | 1 | 1 | 1414541884 |
| 2 | 2 | 0 | 1414576722 |
| 3 | 2 | 0 | 1414577273 |
| 4 | 3 | 0 | 1414782966 |
| 5 | 3 | 1 | 1414785691 |
| 6 | 4 | 0 | 1415112933 |
+----+---------+--------+------------+
此表存储所有付款交易记录。 status = 0表示付款因任何原因失败，status = 1表示付款成功。
我现在想设置一个 cronjob，所有用户都会收到一条消息，他们在过去 7 天内无法购买会员资格。如上例表所示，我只需要 2 行user_id，transaction_id最近付款和最近付款time_stampe。由于user_id = 3在第一次付款失败后设法获得了会员资格，因此他应该被排除在外：
+---------+----------------+------------+
| user_id | transaction_id | time_stamp |
+---------+----------------+------------+
| 2 | 3 | 1414577273 |
| 4 | 6 | 1415112933 |
+---------+----------------+------------+
是否可以在一个查询中完成所有这些工作？如果是，它是否比使用两个查询更有效？
到目前为止，我得到了什么：
SELECT DISTINCT
`t`.`user_id`,
`t`.`id` AS `transaction_id`,
`t`.`time_stamp`
FROM `payment_transaction` AS `t`
WHERE
`t`.`status` = 0
AND `t`.`time_stamp` < UNIX_TIMESTAMP() - 60*60*24*7
ORDER BY
`t`.`id`
但是，它并没有排除user_id = 3的用户，并且还给了我第一次失败的付款，而是最近的付款。
----编辑----
感谢RST在下面的评论中提供解决方案。经过一些其他更改后，最终查询如下所示：
SELECT
`t`.`user_id`,
MAX(`t`.`id`) AS `transaction_id`,
MAX(`t`.`time_stamp`) AS `time_stamp`
FROM `payment_transaction` AS `t`
WHERE
`t`.`status` = 0
AND `t`.`time_stamp` < UNIX_TIMESTAMP() - 60*60*24*7
AND `t`.`user_id` NOT IN (
SELECT `user_id`
FROM `payment_transaction`
WHERE `status` = 1
AND UNIX_TIMESTAMP(`time_stamp`) < UNIX_TIMESTAMP() - 60*60*24*7
)
GROUP BY
`t`.`user_id`
ORDER BY
`t`.`id`
考虑与您的
请求有点不同，但仍应获得相同的结果，具体取决于您对应用程序进行编码的方式。假设最新交易将是最相关的，是否准确？
如果是这样，您可以查看以下内容(未经测试(：
SELECT DISTINCT
`t`.`user_id`,
`t`.`id` AS `transaction_id`,
`t`.`time_stamp`
FROM `payment_transaction` AS `t`
WHERE
`t`.`time_stamp` < UNIX_TIMESTAMP() - 60*60*24*7
GROUP BY
`t`.`user_id`
ORDER BY
`t`.`id`
请注意，我还没有测试过这个SQL，因为我离开了我的开发机器。我建议浏览一下 GROUP BY 命令的复杂性，以确保您不会遇到一些未被捕获的边缘情况。
DROP TABLE IF EXISTS payment_transaction;
CREATE TABLE payment_transaction
(ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,user_id INT NOT NULL
,status INT NOT NULL
,time_stamp BIGINT NOT NULL
);
INSERT INTO payment_transaction VALUES
(1 ,1 ,1 ,1414541884),
(2 ,2 ,0 ,1414576722),
(3 ,2 ,0 ,1414577273),
(4 ,3 ,0 ,1414782966),
(5 ,3 ,1 ,1414785691),
(6 ,4 ,0 ,1415112933);
SELECT x.*
FROM payment_transaction x
JOIN
( SELECT a.user_id
, MAX(a.id) max_id
FROM payment_transaction a
LEFT
JOIN payment_transaction b
ON b.user_id = a.user_id
AND b.status = 1
AND b.time_stamp > a.time_stamp
WHERE a.time_stamp > UNIX_TIMESTAMP(NOW())-604800
AND a.status = 0
AND b.id IS NULL
) y
ON y.user_id = x.user_id
AND y.max_id = x.id;
+----+---------+--------+------------+
| ID | user_id | status | time_stamp |
+----+---------+--------+------------+
| 6 | 4 | 0 | 1415112933 |
+----+---------+--------+------------+