我正在尝试创建一个数据库来管理设备维护.我有两个表:
I am trying to produce a database to manage maintenance of equipment. I have two tables:
我想查询一个显示下次维修日期的查询.到目前为止,我有:
I would like a query that displays the date that it should be next serviced. So far I have:
SELECT Max(DateAdd('m', [Inventory].[Service Period], [WorkDone].[Work Date])) AS NextServiceDate, Inventory.Equipement FROM Inventory INNER JOIN WorkDone ON Inventory.ID = WorkDone.Equipment GROUP BY Inventory.Equipement只要已为给定设备记录了已完成的某些工作,此方法就很好用.如果未执行任何工作,我希望 NextServiceDat e也显示
This works well as long as some work done has been registered for a given piece of equipment. If no work has been carried out I would like the NextServiceDate to also show
DateAdd('m',[Inventory].[Service Period], [Inventory].[Purchase Date])但是,我无法弄清楚如何获得SQL/MS访问以比较两个值,而仅显示两个中较大的一个.通过阅读,我认为我应该能够进行子查询,但是我无法弄清楚如何对其进行阶段化.
However, I cannot work out how to get SQL/MS access to compare two values and only display the greater of the two. From reading around I think I should be able to do a sub-query, but I cannot work out how to phase it.
我一直在尝试从此处调整@MikeTeeVee的答案:.但是我不断收到错误消息,说查询不是聚合函数的一部分,而且我不确定自己做错了什么.例如,我尝试过:
I've been trying to adapt @MikeTeeVee's answer from here: Is there a Max function in SQL Server that takes two values like Math.Max in .NET?. But I keep getting errors saying that query is not part of an aggregate function and I'm not certain what I doing wrong. For example, I tried:
SELECT Inventory.Equipement, (SELECT MAX(NSD_proxy) FROM (VALUES (Max(DateAdd('m', Inventory.[Service Period], WorkDone.[Work Date]))), (DateAdd('m', Inventory.[Service Period], Inventory.[Purchase Date]))) AS FUNCTION(NSD_proxy) ) AS NextServiceDate, FROM Inventory INNER JOIN WorkDone ON Inventory.ID = WorkDone.Equipment GROUP BY Inventory.Equipement存在语法错误.
推荐答案考虑一个LEFT JOIN返回匹配或不匹配的记录,其中记录用NULL填充,然后使用 NZ():
Consider a LEFT JOIN to return matched or unmatched records where latter is filled with NULLs, and then run your aggregate, MAX, with an NZ():
SELECT Max(NZ(DateAdd('m', i.[Service Period], w.[Work Date]), DateAdd('m', i.[Service Period], i.[Purchase Date])) ) AS NextServiceDate, i.Equipement FROM Inventory i LEFT JOIN WorkDone w ON i.ID = w.Equipment GROUP BY i.Equipement更多推荐
MS Access/SQL子查询的语法,包括聚合函数
发布评论