mysql - 仅显示mysql子查询返回null的行

我正在尝试查询收据表，以从数据库中提取第一个交易属于特定时间范围的订单号。这是我到目前为止的内容：

SELECT id, order_number,
(
  SELECT id FROM receipts
  WHERE received < r.received AND order_number = r.order_number
  LIMIT 1
) priorPayment
FROM receipts r
WHERE received > 1416384000 AND received < 1416470399
ORDER BY id DESC

这项工作正常，除了我想过滤掉所有返回了beforePayment的东西（所以只显示此子查询返回null的地方），现在它会返回所有东西。有什么建议么？

最佳答案

尽管您可以为此或having子句使用子查询，但我建议仅使用left join

SELECT r.id, r.order_number
FROM receipts r LEFT JOIN
     receipts r2
     ON r2.received < r.received and r2.order_number = r.order_number
WHERE r.received > 1416384000 AND r.received < 1416470399 AND
      r2.order_number is NULL
ORDER BY r.id DESC;

关于mysql - 仅显示mysql子查询返回null的行，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/28616564/