MS Access/SQL子查询的语法，包括聚合函数 [英] Syntax of MS Access/SQL sub-query including aggregate functions

问题描述

我正在尝试创建一个数据库来管理设备维护.我有两个表:

I am trying to produce a database to manage maintenance of equipment. I have two tables:

1. 一个(库存)，其中包含每台设备的详细信息，包括购买日期和服务期限，
2. 一个包含完成工作的详细信息( WorkDone )，包括完成工作的日期( Work Date ).

1. One (Inventory) containing details of each piece of equipment, including Purchase Date and Service Period,
2. One containing details of work done (WorkDone), including the date the work was carried out (Work Date).

我想查询一个显示下次维修日期的查询.到目前为止，我有:

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子查询的语法，包括聚合函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！