如果当前日期超过24小时的列日期，sql查询将列出所有行

Question

谢谢，我正在使用Mysql数据库。实际上，我需要一行数据，与当前日期和时间相比，其order_date列的datetime数据类型超过24小时。与carts表orderid相比，其他名称为"checks“的表包含相同的orderid。因此，当比较carts、order_date和当前日期时，需要一个sql查询来获得一行carts表数据，而与检查表orderid相同的carts表应该被忽略。

carts table:
S.No | username | orderid             | exam_name | order_date         |
1    | Rakesh   |746G797W819d3343493z | PMP       | 2016-11-05 11:34:5 |
2    | Rakesh   |746G797W819d3343493z | CAPM      | 2016-11-05 11:34:5 |
3    | Kulkarni |24890W819d6743493W   | CAPM      | 2016-11-05 11:34:5 |
4    | Suresh   |52890W819d6743493W   | PMP       | 2016-11-05 11:34:5 |
5    | Gautham  |90890W819d6743493W   | PMP       | 2016-11-05 11:34:5 |

checks table:
S.No | username | orderid             | exam_name | checked_date         
1    | Rakesh   |746G797W819d3343493z | PMP       | 2016-11-07 11:20:5   
2    | Kulkarni |24890W819d6743493W   | CAPM      | 2016-11-07 11:20:5   

其余行如下所需

S.No | username | orderid             | exam_name | order_date         |
1    | Rakesh   |746G797W819d3343493z | CAPM      | 2016-11-05 11:34:5 |
2    | Suresh   |52890W819d6743493W   | PMP       | 2016-11-05 11:34:5 |
3    | Gautham  |90890W819d6743493W   | PMP       | 2016-11-05 11:34:5 |

Answer 1

我要做的事情如下：

SELECT
    *
FROM
    carts
LEFT JOIN
    checks ON
    carts.orderid = checks.orderid AND
    carts.exam_name = checks.exam_name
WHERE 
    checks.orderid is null AND
    checks.exam_name is null AND
    carts.order_date < NOW() - INTERVAL 1 DAY