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语句
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
)
报了一下的错误
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
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);