mysql - SQL 选择特定日期和附加字段过滤器之间的数据

Question

我有一个包含以下列的 sql 表 payments:

id|name|amount|reference_number|payment_date|status

这里有一些值

1|John Doe|10.00|123456G|2019-02-21 08:00:21|confirmed
2|John Doe|10.00|FHFHgJJ|2019-02-21 10:05:50|pending
3|John Doe|10.00|57GHHFG|2019-02-22 12:10:32|pending
4|John Doe|10.00|GHTYNHJ|2019-02-22 09:52:26|confirmed
5|John Doe|10.00|123G456|2019-02-23 12:22:45|confirmed
6|John Doe|10.00|J123456|2019-02-23 13:00:21|pending

现在我需要根据 payment_date 和 status 获取选择数据，其中有时 payment_date 可以是一个范围或相同的日期。

场景 1:

SELECT * 
FROM payments 
WHERE payment_date >= '2019-02-21' AND payment_date <= '2019-02-21' AND status='confirmed' 
ORDER BY id DESC

应该返回:

1|John Doe|10.00|123456G|2019-02-21 08:00:21|confirmed

场景 2:

SELECT * 
FROM payments 
WHERE payment_date >= '2019-02-21' AND payment_date <= '2019-02-23' AND status='confirmed' 
ORDER BY id DESC

应该返回:

1|John Doe|10.00|123456G|2019-02-21 08:00:21|confirmed
4|John Doe|10.00|GHTYNHJ|2019-02-22 09:52:26|confirmed
5|John Doe|10.00|123G456|2019-02-23 12:22:45|confirmed

有解决办法吗？

Answer 1

@strawberry 建议使用时间和日期来获取数据

SELECT * 
FROM payments 
WHERE payment_date >= '2019-02-21 00:00:00' AND payment_date <= '2019-02-21 
23:59:59' AND status='confirmed' 
ORDER BY id DESC