选择列表中的列 'orders.order_id' 无效,因为该列没有包含在聚合函数或 GROUP BY 子句中。

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/ftl111/article/details/79971190


今天进行数据库查询是分组时间出现了:

消息 8120,级别 16,状态 1,第 1 行
选择列表中的列 'orders.order_id' 无效,因为该列没有包含在聚合函数或 GROUP BY 子句中。

的错误,写的sql语句如下:

select o.goods_id ,g.goods_name,sum(goods_number) total

from orders o left join goods g

on o.goods_id=g.goods_id

group by o.goods_id

在网上查询后有猿友说:其实sql语句的执行顺序不是你写代码的顺序,是先group by 再select的,如果group by中都没有出现这列,select的时候肯定报错。


即指定 GROUP BY 时,选择列表中任一非聚合表达式内的所有列都应包含在 GROUP BY 列表中,或者 GROUP BY表达式必须与选择列表表达式完全匹配

最后SQL语句如下:

select o.goods_id ,g.goods_name,sum(goods_number) total

from orders o left join goods g

on o.goods_id=g.goods_id

group by g.goods_name,o.goods_id

运行正常。

无论select 后面跟了几个字段,除了聚合函数如 count sum avg等之外,都要在

group by后出现,否则就会报上述错误。




阅读更多

选择列表中的列“employee.Name”无效因为没有含在聚合函数GROUP BY子句中

01-20

[code=SQL]rnSELECT rn employee.Name,employee.mainSalary, employee_salary.salaryID,section.section_name, employee_salary.overtime, rn employee_salary.phoGrants, employee_salary.bonus,rn employee_salary.houseGrants,rn employee_salary.fine, employee_salary.workClothes, employee_salary.PLX, rn employee_salary.borrower, employee_salary.rent, employee_salary.catering,rn employee_salary.Months, rn YangL,ShiY,YiL,GS,ShengY,rn attendance.attendanceDay,rn (mainSalary - ((mainSalary / (Days - holidays)) * LeaveDay) + ((mainSalary / (Days - holidays)) * (MinersDate * 2))) as AttendancePay, --出勤工资rn ((mainSalary / (Days - holidays)) * LeaveDay) as Leave, --请假扣款rn ((mainSalary / (Days - holidays)) * (MinersDate * 2)) as Miners, --矿工扣款rn ((mainSalary - ((mainSalary / (Days - holidays)) * LeaveDay) + ((mainSalary / (Days - holidays)) * (MinersDate * 2))) + overtime + phoGrants + bonus + houseGrants) as YingFaGongZi, --应发工资rn ((mainSalary - ((mainSalary / (Days - holidays)) * LeaveDay) + ((mainSalary / (Days - holidays)) * (MinersDate * 2))) + overtime + phoGrants + bonus + houseGrants)rn - (fine + workClothes + PLX + borrower + rent + catering + YangL + ShiY + YiL + GS + ShengY ) as RealWages, --实发工资rn sum(((mainSalary - ((mainSalary / (Days - holidays)) * LeaveDay) + ((mainSalary / (Days - holidays)) * (MinersDate * 2))) + overtime + phoGrants + bonus + houseGrants)rn - (fine + workClothes + PLX + borrower + rent + catering + YangL + ShiY + YiL + GS + ShengY )) as sums --合计rn FROM employee_salary INNER JOINrn employee ON employee_salary.employeeID = employee.employeeID INNER JOINrn attendance ON rn employee_salary.attendanceID = attendance.attendanceID INNER JOINrn section ON employee.sectionID = section.section_id where isLeave = 'false'and employee_salary.Months = @Months order by section.section_idrnrn[/code]

没有更多推荐了,返回首页