Mysql>获取特定日期的汽车和汽车库存数据，但如果特定日期不存在汽车库存，则仅显示具有空库存的汽车数据
我创建了一个系统，其中有两个表，即汽车和汽车库存。 汽车表存储经销商销售的汽车列表，汽车库存表存储编号。每辆至少有一辆库存的汽车的汽车数量。 请建议一个 SQL 查询，它给出两行，即“汽车名称”和“车号”。库存汽车”。 汽车名称和汽车库存数量 对于库存为零的汽车，第二列应写为 Null。
我尝试过下面的查询，它对于较小的数据工作正常，但对于较大的数据需要太多时间。
查询：
SELECT cars.id,
cars.name,
car_stocks.created_at
FROM `cars`
LEFT JOIN car_stocks ON car_stocks.car_id = cars.id and date(car_stocks.created_at) = '2022-01-18';
预期结果示例：
如果您有更优化/耗时更少的查询，请提出建议
I have created a system where I have two tables namely cars and car stocks.
Cars table stores the list of cars that the dealer sells and Car stocks table stores the no. of cars for each car that has at least one piece in stock.
Please suggest a SQL query which gives two rows namely 'Car name' and 'No. of cars in stock'. car_name and car_stock_qty
For a car that has zero stock , Null should be written on the second column .
I have tried below query, which works fine for smaller data, but takes too much time for larger data.
Query:
SELECT cars.id,
cars.name,
car_stocks.created_at
FROM `cars`
LEFT JOIN car_stocks ON car_stocks.car_id = cars.id and date(car_stocks.created_at) = '2022-01-18';
Example expected result:
Please suggest if you have a more optimized/less time consuming query
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您可以尝试为
car_stockID 和created_at创建索引，我认为这是您能达到的最快速度。You can try creating an index for the
car_stockID andcreated_at, I think that's as fast as you can get.