SQL: STATUS列在select列表中无效，因为它既不包含在聚合函数中，也不包含在GROUP BY子句中

Question

嗨，我只是想对我的表进行分组，而不使用状态列。但是我总是得到错误，我不知道如何解决。

SELECT * FROM ( SELECT COUNT(DOCNUM) 
AS 
count_idoc, 
CREDAT,
[STATUS],
MESTYP,
DIRECT,
RCVPRN,
SNDPRN,

(CASE 
WHEN [STATUS] = '03' THEN 'IDOC Sent'
WHEN [STATUS] = '53' THEN 'IDOC Received'
WHEN [STATUS] = '30' THEN 'Waiting to be processed'
WHEN [STATUS] = '64' THEN 'Waiting to be processed'
WHEN [STATUS] > '42' THEN 'IDOC not posted'
WHEN [STATUS] < '42' THEN 'IDOC not sent'
END) AS 'status_msg'
FROM [MIA-Time]
GROUP BY CREDAT,MESTYP,DIRECT,RCVPRN,SNDPRN
) status_result

Answer 1

感谢@ james的宝贵评论，并将其作为答案发布。正如james在评论中提到的，您的group by列表中缺少[STATUS]列。

所有不属于聚合的列都应该包含在group by列表中。

SELECT * FROM 
( SELECT COUNT(DOCNUM) AS count_idoc, 
CREDAT,
[STATUS],
MESTYP,
DIRECT,
RCVPRN,
SNDPRN,
(CASE 
WHEN [STATUS] = '03' THEN 'IDOC Sent'
WHEN [STATUS] = '53' THEN 'IDOC Received'
WHEN [STATUS] = '30' THEN 'Waiting to be processed'
WHEN [STATUS] = '64' THEN 'Waiting to be processed'
WHEN [STATUS] > '42' THEN 'IDOC not posted'
WHEN [STATUS] < '42' THEN 'IDOC not sent'
END) AS 'status_msg'
FROM [MIA-Time] 
GROUP BY CREDAT,[STATUS],MESTYP,DIRECT,RCVPRN,SNDPRN
) status_result