Column ‘xxxx‘ is invalid in the select list because it is not contained in either an

[42000] [Microsoft][SQL Server Native Client 10.0][SQL Server]Column ‘xxxx’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. (8120)

问题是GROUP BY分组中的字段，不全。应该把查询的所有字段放入分组当中才可以

• 方式一： 将查询的所有字段都放入group by 后即可；
• 方式二： 不想将查询的所有字段放入group by后，解决：增加聚合函数(MIN/MAX()/ROW_NUMBER())；

---

• 案例一、ROW_NUMBER()

SELECT 
ROW_NUMBER() over(order by man.HolderCode) as codes,
man.HolderName,IODateTime
FROM farm_info info 
RIGHT JOIN menjinregion gion ON info.reserve_a = gion.RegionNo
RIGHT JOIN menjinman man ON gion.IOGateNo = man.IOGateNo
WHERE info.reserve_a = 'D2137'
GROUP BY man.HolderCode

• 案例二、MAX()

SELECT 
max(man.HolderCode) as HolderCode,
max(man.HolderName) as HolderName,
max(man.IODateTime) as IODateTime
FROM farm_info info 
RIGHT JOIN menjinregion gion ON info.reserve_a = gion.RegionNo
RIGHT JOIN menjinman man ON gion.IOGateNo = man.IOGateNo
WHERE info.reserve_a = 'D2137'
GROUP BY man.HolderCode

如果是MySQL的话

select @@sql_mode;

默认开启了 only_full_group_by 模式

完 毕 ，over ！