一个很简单的sql问题
with
t1 as
(
select 'jones' as name,2975 as sal,10 as deptno from dual
union all
select 'scott' as name,3000 as sal,10 as deptno from dual
union all
select 'ford' as name,3000 as sal,20 as deptno from dual
union all
select 'sakl' as name,2500 as sal,10 as deptno from dual
union all
select 'wh' as name,1100 as sal,10 as deptno from dual
union all
select 'jjj' as name,11000 as sal,20 as deptno from dual
)
,
t2 as
(
select 10 as deptno,'A组' as depname from dual
union all
select 20 as deptno,'B组' as depname from dual
)
--select * from t1
--select * from t2
select avg(t1.sal),t2.depname from t1 left join t2 on t1.deptno = t2.deptno
group by t2.depname
以上为数据
问题描述:求工资大于3000的部门
select avg(t1.sal),t2.depname from t1 left join t2 on t1.deptno = t2.deptno
group by t2.depname where avg(t1.sal) > 3000报错
求sql
[解决办法]
group by t2.depname where avg(t1.sal) > 3000
→
group by t2.depname having avg(t1.sal) > 3000
