一个很简单的sql问题

[wanghao2979]

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 count(*),depname from t1 left join t2 on t1.deptno = t2.deptno
group by depname


问题描述:查询员工人数高于各部门平均人数的部门

部门平均人数:3人

问题正确结果是:4 A组

要求:查询次数要少

求sql语句

[善若止水]

好神奇的错误呀，真没有弄明白是怎么回事？
SELECT t2.depname,count(*) FROM t1,t2
WHERE t1.deptno=t2.deptno
GROUP BY t2.depname
HAVING COUNT(*)>
(
SELECT COUNT(*)/COUNT(DISTINCT t1.deptno) FROM t1
)
报了一下的错误

[wanghao2979]

DISTINCT 是我们项目组绝对不可以有的语句,效率太底,

[RicharDu]

除了楼上的，还可以这样写 select cnt,depname from ( select cnt, depname ,avg(cnt) over () as avg_cnt from ( select count(*) cnt,depname from t1 left join t2 on t1.deptno = t2.deptno group by depname) ) where cnt > avg_cnt

[crazy_samba]

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
 ),
 t3 as
 (select count(*) num,depname from t1 left join t2 on t1.deptno = t2.deptno
 group by depname)
select * from t3 where num > (select avg(num) from t3);

[善若止水]

引用 3 楼 S2060113 的回复:

除了楼上的，还可以这样写 select cnt,depname from ( select cnt, depname ,avg(cnt) over () as avg_cnt from ( select count(*) cnt,depname from t1 left join t2 on t1.deptno = t2.deptno group b……

学习了，分析函数，佩服