--try select A.* ,B.Salary from t_Dept A inner join (select Dept_ID,avg(Salary) as Salary from t_Salary group by Dept_ID having avg(Salary)>3000) B on A.ID=B.Dept_ID
select Dept_ID,avg(Salary) as 平均工资 from t_Salary group by Dept_ID having avg(Salary)>3000
t_Salary(ID,Dept_ID,Name,Salary) 工资表 t_Dept(ID,Dept_Name) 部门表 求部门中平均工资大于3000的员工所在部门的平均工资????? ----- select * from ( select Dept_ID,avg(Salary) from t_Salary group by Dept_ID having avg(Salary)>3000) TS inner join t_Dept TD on TS.Dept_ID=TD.ID
select b.Dept_Name as 部门,avg(a.Salary) as 平均工资 from t_Salary a left join t_Dept b on a.Dept_ID = b.ID group by b.Dept_Name having avg(a.Salary) >= 3000
HAVING 子句对 GROUP BY 子句设置条件的方式与 WHERE 子句和 SELECT 语句交互的方式类似。WHERE 子句搜索条件在进行分组操作之前应用;而 HAVING 搜索条件在进行分组操作之后应用。HAVING 语法与 WHERE 语法类似,但 HAVING 可以包含聚合函数。HAVING 子句可以引用选择列表中出现的任意项。我贴这段也是为了自己学习下,加深下印象,呵呵
SELECT A.ID, A.DEPT_NAME, '平均工资'=SUM(B.SALARY)/COUNT(B.ID) FROM T_DEPT A LEFT JOIN T_SALARY B ON A.ID=B.DEPT_ID GROUP BY A.ID, A.DEPT_NAME HAVING COUNT(B.ID)<>0 AND SUM(B.SALARY)/COUNT(B.ID)>3000 ------------------------------------------------ 要注意可能存在科室没有工资信息的情况
select A.* ,B.Salary from t_Dept A
inner join
(select Dept_ID,avg(Salary) as Salary from t_Salary group by Dept_ID having avg(Salary)>3000) B
on A.ID=B.Dept_ID
from t_Salary
group by Dept_ID
having avg(Salary)>3000
t_Dept(ID,Dept_Name) 部门表
求部门中平均工资大于3000的员工所在部门的平均工资?????
-----
select *
from
(
select Dept_ID,avg(Salary)
from t_Salary
group by Dept_ID
having avg(Salary)>3000) TS
inner join t_Dept TD
on TS.Dept_ID=TD.ID
left join t_Dept b on a.Dept_ID = b.ID
group by b.Dept_Name having avg(a.Salary) >= 3000
A.ID,
A.DEPT_NAME,
'平均工资'=SUM(B.SALARY)/COUNT(B.ID)
FROM
T_DEPT A LEFT JOIN T_SALARY B ON A.ID=B.DEPT_ID
GROUP BY
A.ID,
A.DEPT_NAME
HAVING
COUNT(B.ID)<>0
AND SUM(B.SALARY)/COUNT(B.ID)>3000
------------------------------------------------
要注意可能存在科室没有工资信息的情况