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
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
解决方案 »
- java.sql.SQLException: ORA-00907: 缺失右括号
- DBMS_OUTPUT的FLUSH问题
- 多表查询的问题
- 用用户user1建立两个表空间后,如何分别查询出每个表空间各自有多少个用户表呢?
- 变异表触发器:当向test1表插入数据后,统计test1表中sum(value1),插入到表test2中。
- sql 合并单元格问题
- 在SQL PLUS如何查看所有系统表,用户表?
- 循环触发的问题
- 我在另外一台机器上装了developer 6i, 为什么开发form时,连不上服务器上的数据库,要怎么配置??
- 有个trace文件,一直在涨,停不掉,该如何处理?
- 数据库连接不上
- oracle 的外连接 left join
→
group by t2.depname having avg(t1.sal) > 3000