我有一个表拍卖和一个表很多：
mysql> select id, auction_name, auction_startdate, auction_planned_closedate from auctions;
+----+--------------+---------------------+---------------------------+
| id | auction_name | auction_startdate | auction_planned_closedate |
+----+--------------+---------------------+---------------------------+
| 1 | Auction 1 | 2016-05-01 00:00:00 | 2016-06-30 00:00:00 |
| 2 | Auction 2 | 2016-06-01 00:00:00 | 2016-07-30 00:00:00 |
| 3 | Auction 3 | 2016-07-01 00:00:00 | 2016-08-30 00:00:00 |
| 4 | Auction 4 | 2016-09-01 00:00:00 | 2016-10-30 00:00:00 |
+----+--------------+---------------------+---------------------------+
mysql> select id, auction_id, lot_name from lots;
+----+------------+----------+
| id | auction_id | lot_name |
+----+------------+----------+
| 1 | 1 | Lot 1 |
| 2 | 1 | Lot 2 |
| 3 | 1 | Lot 3 |
| 4 | 1 | Lot 4 |
| 5 | 1 | Lot 5 |
| 6 | 1 | Lot 6 |
| 7 | 1 | Lot 7 |
| 8 | 2 | Lot 8 |
| 9 | 2 | Lot 9 |
| 10 | 2 | Lot 10 |
| 11 | 3 | Lot 11 |
| 12 | 3 | Lot 12 |
| 13 | 3 | Lot 13 |
| 14 | 3 | Lot 14 |
| 15 | 4 | Lot 15 |
| 16 | 4 | Lot 16 |
+----+------------+----------+
我只想显示当前拍卖的批次（在示例中为拍卖1和2），换句话说，当前时间在“拍卖开始日期”和“拍卖结束日期”之间。
所以这是我要实现的目标：
+--------------+---------------------+---------------------------+---------+
| auction_name | auction_startdate | auction_planned_closedate | lots_id |
+--------------+---------------------+---------------------------+---------+
| Auction 1 | 2016-05-01 00:00:00 | 2016-06-30 00:00:00 | 1 |
| Auction 1 | 2016-05-01 00:00:00 | 2016-06-30 00:00:00 | 2 |
| Auction 1 | 2016-05-01 00:00:00 | 2016-06-30 00:00:00 | 3 |
| Auction 1 | 2016-05-01 00:00:00 | 2016-06-30 00:00:00 | 4 |
| Auction 1 | 2016-05-01 00:00:00 | 2016-06-30 00:00:00 | 5 |
| Auction 1 | 2016-05-01 00:00:00 | 2016-06-30 00:00:00 | 6 |
| Auction 1 | 2016-05-01 00:00:00 | 2016-06-30 00:00:00 | 7 |
| Auction 2 | 2016-06-01 00:00:00 | 2016-07-30 00:00:00 | 8 |
| Auction 2 | 2016-06-01 00:00:00 | 2016-07-30 00:00:00 | 9 |
| Auction 2 | 2016-06-01 00:00:00 | 2016-07-30 00:00:00 | 10 |
+--------------+---------------------+---------------------------+---------+
以下查询可让我了解当前的拍卖情况：
mysql> select auction_name, auction_startdate, auction_planned_closedate from auctions where now() >= auction_startdate and now() <= auction_planned_closedate;
+--------------+---------------------+---------------------------+
| auction_name | auction_startdate | auction_planned_closedate |
+--------------+---------------------+---------------------------+
| Auction 1 | 2016-05-01 00:00:00 | 2016-06-30 00:00:00 |
| Auction 2 | 2016-06-01 00:00:00 | 2016-07-30 00:00:00 |
+--------------+---------------------+---------------------------+
然后使用“ lots”表进行内部联接：
select auction_name, auction_startdate, auction_planned_closedate, lots.id
from auctions
where now() >= auction_startdate
and now() <= auction_planned_closedate
inner join lots on auctions.id = lots.auction_id;
错误1064（42000）：您的SQL语法有错误；检查
与您的MySQL服务器版本相对应的手册
在以下位置使用“ inner join lot on auctions.id = lots.auction_id”附近的语法
1行
我遇到语法错误，我盯着一会儿。
最佳答案
顺序错误，将WHERE子句放在JOIN之后：
select auction_name, auction_startdate, auction_planned_closedate, lots.id
from auctions
inner join lots on auctions.id = lots.auction_id
where now() >= auction_startdate
and now() <= auction_planned_closedate
关于mysql - SQL查询:查找属于当前拍卖的批次，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/37747244/