mysql - 错误代码:1054。“字段列表”中的未知列“部门”0.000 秒
问题描述
我已经经历过:错误代码:1248。每个派生表都必须有自己的别名 No solution found for query,但仍然不确定它期望什么正确的列名?
情况1:
SELECT max(avg_sal), min(avg_sal)
FROM (SELECT dept , avg(salary) as avg_sal from Employee e GROUP BY dept HAVING dept in ('Admin','IT'));
12:04:44 SELECT max(avg_sal), min(avg_sal) FROM (SELECT dept , avg(salary) as avg_sal from Employee e GROUP BY dept HAVING dept in ('Admin','IT')) LIMIT 0, 1000 错误代码:1248。每个派生表都必须有自己的别名 0.000 秒
案例:2
SELECT max(avg_sal), min(avg_sal)
FROM (SELECT dept , avg(salary) as avg_sal from Employee e GROUP BY dept HAVING dept in ('Admin','IT')) as dept;
然后我得到
SELECT max(avg_sal), min(avg_sal) FROM (SELECT dept , avg(salary) as avg_sal from Employee e GROUP BY dept HAVING dept in ('Admin','IT')) 作为部门;
部门
@Entity
public class Dept {
@Id
@GeneratedValue
private Integer id;
private String name;
}
员工
@Entity
@NamedQuery(name = "Employee.findMaxSalariesByDept",
query = "SELECT e.dept, MAX(e.salary) FROM Employee e GROUP BY e.dept.name HAVING e.dept.name in ?1")
public class Employee {
@Id
@GeneratedValue
private Long id;
private String name;
@ManyToOne(cascade = CascadeType.ALL)
private Dept dept;
private int salary;
}
显示表:
CREATE TABLE `employee` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`salary` int(11) NOT NULL,
`dept_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `FKfow2bhgypdy2ij4oyukrn6cqw` (`dept_id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
解决方案
第一种情况,您需要 FROM() 子句的表别名,例如: FROM ( ) T
SELECT max(T.avg_sal)
, min(T.avg_sal)
FROM (
SELECT dept_id dept, avg(salary) as avg_sal
from Employee e
GROUP BY dept_id
WHERE dept_id in ('Admin','IT')
) T ;
你应该在没有过滤器的地方使用
第二种情况(不要为表别名使用与列相同的名称,例如:dept,但使用 t_dept)
SELECT max(t_dept.avg_sal), min(t_dept.avg_sal)
FROM (
SELECT dept_id dept, avg(salary ) as avg_sal
from Employee e
WHERE dept_id in ('Admin','IT')
GROUP BY dept
) as t_dept;
并且您可以使用 where 过滤器来执行这些值未计算但在行内容中可用的事实
推荐阅读
- r - 将 R `outer` 与 `%in%` 运算符一起使用
- python - 是否可以使用 ModelViewSets 在列表视图上发出 PUT 请求?
- scala - Spark Frameless withColumnRenamed 嵌套字段
- php - 使用 Laravel 获取目录中的文件数
- python - pandas 在数据集第一次进入之前开始重新采样
- javascript - javascript:使用来自选择的查询字符串刷新同一页面
- three.js - 半透明网格的阴影太暗
- jmeter - 如何在 Jmeter 中动态注入令牌列表
- android - 在 android studio 中创建构建变体源集时出错
- spring-security - Spring Session/Redis 和 Oauth2 不能一起工作