mysql - 如何选择小于值的最新记录

Question

我有一个名为conductor的表。我想选择日期小于my_value的最新记录。

+----+-----------+------+
| id | program   | date |
+----+-----------+------+
| 1  | program 1 | 1    |
| 2  | program 1 | 3    |
| 3  | program 2 | 3    |
| 4  | program 1 | 5    |
| 5  | program 1 | 7    |
+----+-----------+------+

如果我们认为 my_value 是 4，那么输出将是:

+----+-----------+------+
| id | program   | date |
+----+-----------+------+
| 2  | program 1 | 3    |
| 3  | program 2 | 3    |
+----+-----------+------+

如何通过 SQL 选择记录？

Answer 1

SELECT * FROM Conductor
WHERE `date` = (SELECT max(`date`) FROM Conductor
                WHERE `date` < myvalue )