我有两个表,一个是需要定期检查的项目,另一个是这些项目的检查报告。
项目可以标记为"不可检验";如果工程师因为某种原因看不到他们。
我需要显示项目列表,显示最后一个实际的每个项目的检验日期。如果该物品的最后一次实际检查与最后一次访问日期(即最后一次尝试检查)不同,则需要突出显示。
以下是我目前的记录:
表和列:
Items
ID, Name, etc...
ReportItems
ReportID, ItemID, InspectionDate, NotAvailable
开始狡猾的伪代码:
SELECT * FROM Items
' then loop through that to display the data:
For each itemDataRow
' Get last actual date
datatable1 = SELECT TOP 1 ReportID, InspectionDate FROM ReportItems WHERE ItemID = itemDataRow(ID) AND NotAvailable = 0 ORDER BY InspectionDate DESC
' Get last attempted date
datatable2 = SELECT TOP 1 ReportID FROM ReportItems WHERE ItemID = itemDataRow(ID) ORDER BY InspectionDate DESC
' Check if they are the same
itemWasNotAvailableAtLastVisit = datatable1(ReportID) <> datatable2(ReportID)
' add row to list to display, showing datatable1(InspectionDate), highlighted if itemWasNotAvailableAtLastVisit == true
End loop
问题是,这意味着在填充数据时对每个项进行2次额外的数据库查询,这会大大降低速度,特别是在较长的列表中。
我如何优化这一点,理想情况下进入一个单一的快速查询?
我已经尝试过这样的子查询,但它非常慢:
SELECT ID, Name, (
SELECT TOP 1 InspectionDate FROM ReportItems WHERE ItemID = Items.ID AND Available = 0 ORDER BY InspectionDate DESC
) AS LastInspection,
(
SELECT TOP 1 InspectionDate FROM ReportItems WHERE ItemID = Items.ID ORDER BY InspectionDate DESC
) AS LastVisit
FROM Items
样本数据:
项目ID | Name
---------
1 | Crane
2 | Tractor
3 | Forklift
ReportItems
ReportID | VisitID | ItemID | InspectionDate | NotAvailable
----------------------------------------------------------------
1 | 1 | 1 | 2022-04-20 | 0
2 | 1 | 2 | 2022-04-20 | 1
3 | 1 | 3 | 2022-04-20 | 0
4 | 2 | 1 | 2022-03-15 | 0
5 | 2 | 2 | 2022-03-15 | 0
6 | 2 | 3 | 2022-03-15 | 0
所需结果:
Crane, last inspection was 2022-04-20
Tractor, last inspection was **2022-03-15** (NB This is not the last visit date)
Forklift, last inspection was 2022-04-20
根据您的示例查询,您可能可以按照以下方式加入/group/max:
SELECT ID, [Name]
, MAX(CASE WHEN Available = 0 THEN InspectionDate END) AS LastInspection
, MAX(InspectionDate) AS LastVisit
FROM Items I
JOIN ReportItems RI on RI.ItemID = I.ID
GROUP BY I.ID;