我正在尝试编写一些access SQL来查询报告并计算优先级

Question

我是新手，所以请耐心等待，我正在尝试编写一些访问SQL，它将查询报告并计算优先级，

每一行都有设备编号、优先级和日期。

我想要计算的是，对于优先级为“Q”且最早日期为1的每个设备编号，优先级为“Q”= 2的下一个相同设备编号，以及优先级为“Q”=3的下一个相同设备编号，每次有新的设备编号时，计数将从我尝试过的1个SQL重新开始

SELECT SERVICES.*, (SELECT COUNT([PRIORITY]) FROM SVC WHERE SERVICES.ID=ID) And [SERVICES].[PRIORITY]="Q" AS Count1
FROM SVC AS SERVICES
ORDER BY SERVICES.ID;

此代码生成和-1

​

​

​

​

Answer 1

您的查询未考虑PLANDATE_MAINTCALL：

SELECT S.*,
       (SELECT COUNT(*)
        FROM SVC as S2
        WHERE S2.EQUIPEMENT = s.EQUIPMENT AND
              S2.PLANDATE_MAINTCALL <= S.PLANDATE_MAINTCALL AND
              S2.[PRIORITY] = "Q"
       ) AS Count1
FROM SVC AS S
ORDER BY S.ID;

Answer 2

此表达式：

(SELECT COUNT([PRIORITY]) FROM SVC WHERE SERVICES.ID=ID) And [SERVICES].[PRIORITY]="Q"

包含2个操作数。

第一个是：

(SELECT COUNT([PRIORITY]) FROM SVC WHERE SERVICES.ID=ID)

它返回一个整数，第二个是：

[SERVICES].[PRIORITY]="Q"

它是布尔值，对于True返回-1，对于False返回0。

因此，您要做的是对这些表达式的结果应用运算符AND，最终结果是一个布尔值:对于True为-1，对于False为0。

我怀疑你想要的是：

SELECT SERVICES.*, 
  (SELECT COUNT([PRIORITY]) FROM SVC WHERE SERVICES.ID=ID) AS Count1
WHERE [SERVICES].[PRIORITY]="Q" 
FROM SVC AS SERVICES
ORDER BY SERVICES.ID;

或者：

SELECT SERVICES.*, 
  (SELECT COUNT([PRIORITY]) FROM SVC WHERE SERVICES.ID=ID AND [SERVICES].[PRIORITY]="Q") AS Count1
FROM SVC AS SERVICES
ORDER BY SERVICES.ID;

虽然没有样本数据，但很难判断。