select worksalary,avg(worksalary),workname,worksex,innum from worker,inhotel where worker.worknum=inhotel.worknum group by avg(worksalary) order by worksalary asc; ERROR 1056 (42000): Can't group on 'avg(worksalary)'
时间: 2024-02-28 16:55:51 浏览: 18
这个错误是因为你尝试在 GROUP BY 子句中使用了聚合函数 avg(worksalary),而正确的做法是在 SELECT 子句中使用聚合函数并在 GROUP BY 子句中添加非聚合字段。另外,如果你想按照 worksalary 升序排列,应该在 ORDER BY 子句中写成 worksalary ASC,而不是 avg(worksalary) ASC。
你可以尝试如下 SQL 语句:
```
SELECT worker.worksalary, AVG(worker.worksalary), worker.workname, worker.worksex, inhotel.innum
FROM worker
INNER JOIN inhotel
ON worker.worknum = inhotel.worknum
GROUP BY worker.worksalary, worker.workname, worker.worksex, inhotel.innum
ORDER BY worker.worksalary ASC;
```
这个语句会先将 worker 表和 inhotel 表进行内连接,然后按照 worksalary、workname、worksex 和 innum 字段进行分组,并计算每组的平均 worksalary。最后按照 worksalary 升序排列。
相关问题
select room.roomtype,inhotel.innum,worker.workname from room -> join inhotel on inhotel.roomnum=room.roomnum join (select workname from worker)workname on inhotel.worknum=worker.worknum; ERROR 1054 (42S22): Unknown column 'worker.workname' in 'field list'
根据你提供的 SQL 语句,错误提示是无法识别 "worker.workname" 这个列名,这是因为你在子查询中创建了一个名为 "workname" 的子查询表,但你没有给它一个别名,所以在主查询中无法引用它。
可以尝试修改 SQL 语句,给子查询表 "workname" 添加一个别名,例如:
```
SELECT room.roomtype, inhotel.innum, w.workname
FROM room
JOIN inhotel ON inhotel.roomnum = room.roomnum
JOIN (SELECT workname FROM worker) AS w ON inhotel.worknum = w.worknum;
```
这样应该可以解决问题。
select room.roomtype,inhotel.innum,worker.workname from room -> join inhotel on inhotel.roomnum=room.roomnum -> join(select workname from worker)worker on inhotel.worknum=worker.worknum; ERROR 1054 (42S22): Unknown column 'worker.worknum' in 'on clause'
根据你提供的 SQL 语句,错误提示是无法识别 "worker.worknum" 这个列名,这是因为你在子查询中创建了一个名为 "worker" 的子查询表,但是你在主查询中的 JOIN 子句中使用了错误的表别名。
应该将子查询表的别名 "worker" 修改为 "w" 或其他别名,如下所示:
```
SELECT room.roomtype, inhotel.innum, worker.workname
FROM room
JOIN inhotel ON inhotel.roomnum = room.roomnum
JOIN (SELECT workname, worknum FROM worker) AS w ON inhotel.worknum = w.worknum
JOIN worker ON w.workname = worker.workname;
```
这样应该可以解决问题。