如何正确创建SQL查询 [英] How to Properly Create a SQL Query

问题描述

下午好，

我正在为某个数据库工作，该数据库可以跟踪他们在业务中使用的车辆.由于车辆在到期时会签发新的车牌，因此企业希望跟踪每辆车的当前车牌以及之前已签发给每辆车的所有车牌.

I am working on a database for someone that tracks the vehicles they use in their business. Due to vehicles having new license plates issued to them as they expire the business wants to track the current plate for each vehicle as well as all plates that were previously issued to each vehicle.

我创建了一个tbl_vehicles车辆表.

I have created a table for vehicles, tbl_vehicles.

我还为车牌tbl_license_plates创建了一个表.

I have also created a table for license plates, tbl_license_plates.

每辆车都有多个车牌记录.我需要在表格frm_vehicles(其中每辆车的某些数据已更新)上仅显示要显示的最新车牌号.即使车牌已经过期，也需要发生这种情况...即...不再有效.

Being that each vehicle has multiple license plate records. I need on a form, frm_vehicles, (where certain data for each vehicle is updated) to show only the most recent license plate number to appear. This needs to happen even if the plate has expired...i.e...is no longer valid.

我遇到的问题是我没有足够的SQL技能来构建仅对每辆车返回最新车牌的查询.我编写了以下查询，并返回了vehicle_master_id和最近发布的车牌的到期日期.

The problem that I am encountering is that I do not have sufficient SQL skills to construct a query that returns for each vehicle only the most recent plate. I wrote the below query and it returns the vehicle_master_id and the expiration date of the most recently issued plate.

但是，当我尝试将license_plate_number添加到查询中时，它将返回为每辆车发布的每张车牌.这是一个问题，因为无论查询是否有效(未过期)，我都需要查询以仅返回最近发布的板块.

However, when I try to add the license_plate_number to the query, it returns every plate that has every been issued for each vehicle. This is a problem because I need the query to return only the most recently issued plate, whether or not it is valid (unexpired).

因此，我正在寻找的指导方针是如何构造此查询，以便无论有效性如何，它仅针对最近发布的车牌返回license_plate_number.

So, the guidance that I am seeking is how to construct this query so that it returns the license_plate_number for only the most recently issued plate, regardless of validity.

有人可以向我指出正确的方向吗?

Can someone point me in the right direction, please?

如所建议的，这是查询的文本

As suggested, here is the text of the query

选择tbl_license_plates.vehicle_master_id AS车辆，最大(tbl_license_plates.date_expires)AS Expiration_Date，tbl_license_plates.license_plate_number 来自tbl_license_plates GROUP BY tbl_license_plates.vehicle_master_id，tbl_license_plates.license_plate_number;

SELECT tbl_license_plates.vehicle_master_id AS Vehicle, Max(tbl_license_plates.date_expires) AS Expiration_Date, tbl_license_plates.license_plate_number FROM tbl_license_plates GROUP BY tbl_license_plates.vehicle_master_id, tbl_license_plates.license_plate_number;

推荐答案

考虑:

SELECT tbl_License_Plates.* FROM tbl_License_Plates WHERE license_plate_master_ID IN  
(SELECT TOP 1 license_plate_master_ID FROM tbl_License_Plates AS Dupe WHERE  
Dupe.vehicle_master_ID = tbl_License_Plates.vehicle_master_ID ORDER BY date_issued DESC);

有关子查询的更多信息 http://allenbrowne.com/subquery-01.html

More info on subqueries http://allenbrowne.com/subquery-01.html

另一种方法是使用具有车辆ID和发布日期以及带有tbl_License_Plates的INNER JOIN的GROUP BY查询，该ID在ID和日期字段上均具有复合链接.这将是不可更新的查询.

Another approach is to take your GROUP BY query that has vehicle ID and issue date and INNER JOIN with tbl_License_Plates with compound link on both ID and date fields. This will be a non-updatable query.

这篇关于如何正确创建SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！