ms-access - 您的查询不包含指定表达式“ID”作为聚合函数的一部分。MS 访问

Question

嗨亲爱的开发人员朋友，我正在尝试以下代码，但出现此错误：您的查询不包含指定的表达式“ID”作为聚合函数的一部分。

代码是

SELECT ID, Date, Time, Status, BoxType, Material, Rack, EmployeeNr, Transaction FROM Records 
WHERE Transaction<100 AND ID NOT IN 
(SELECT `ID` FROM Records a WHERE EXISTS 
   (SELECT `ID` FROM records b WHERE b.Transaction>100 AND (b.Date=a.Date) AND (b.Time > a.Time) AND (b.Transaction-100=a.Transaction))) 
UNION 
SELECT ID, Date, Time, Status, BoxType, Material, Rack, EmployeeNr, Transaction FROM records a WHERE EXISTS 
(SELECT `ID` FROM records b WHERE b.Transaction>100 AND (b.date=a.date) AND (b.time > a.time) AND (b.Transaction-100=a.Transaction)) 
GROUP BY Transaction 
HAVING COUNT(Transaction)>1

标签： ms-access

Answer 1

您既按交易进行分组又对交易进行计数，这没有多大意义。

如果依靠一个字段，则必须以某种方式对其余字段进行分组或聚合。此外，保留字必须用括号括起来（状态和事务可能也需要），并且您不会提取字符串"ID"，而是从子查询中提取字段。

因此，由于我们不知道您要实现什么目标，因此不可能有直接的解决方案，但这应该可以帮助您入门：

SELECT 
    ID, [Date], [Time], Status, BoxType, Material, Rack, EmployeeNr, Transaction 
FROM 
    Records 
WHERE 
    Transaction < 100 AND ID NOT IN 
    (SELECT ID FROM Records a WHERE EXISTS 
        (SELECT ID FROM Records b WHERE 
        b.Transaction>100 AND (b.Date=a.Date) AND (b.Time > a.Time) AND (b.Transaction-100=a.Transaction))) 
UNION 
SELECT 
    ID, [Date], [Time], Status, BoxType, Material, Rack, EmployeeNr, Transaction 
FROM 
    Records a 
WHERE EXISTS 
    (SELECT ID FROM Records b WHERE 
        b.Transaction>100 AND (b.date=a.date) AND (b.time > a.time) AND (b.Transaction-100=a.Transaction)) 
GROUP BY 
    ID, [Date], [Time], Status, BoxType, Material, Rack, EmployeeNr, Transaction 
HAVING 
    COUNT(Transaction) > 1