查询不包含指定的表达式"ID"作为聚合函数的一部分。微软配件

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

Answer 1

你既按事务分组又按事务计数，这没有多大意义。

如果对一个字段进行计数，则其余字段必须以某种方式分组或聚合。此外，保留字必须括起来(Status和Transaction也可能需要)，并且您不会从子查询中提取字符串"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