- 各个部门各个岗位的薪水和大于3000
select
deptno,job,sum(sal)
from emp
group by deptno,job
having sum(sal)>3000;(having后面一般跟聚合函数)
子表
select t.* from
(select
deptno,job,sum(sal) as sum_sal
from emp
group by deptno,job) as t
where t.sum_sal>3000 - 组合
select
deptno,job,sum(sal)
from emp
where job=‘SALESMAN’
group by deptno,job
having sum(sal)>3000
order by sum(sal) desc
limit 1; - join语法
inner join 内连接/等值连接
left join 左连接
right join 右连接
创建表 a b
left join 以左表数据为主,且最全,右表去匹配左表,匹配不到则为null
right join 以右表数据为主,且最全,左表去匹配(on后面的字段)
inner join 两个表匹配的数据行才显示,都有的才显示 - 想知道员工所属部门是什么、在什么地方、所属工资等级是什么?
select
a.ename,a.deptno,(a.sal+IFNULL(a.comm, 0)) as salcomm,
s.grade,
b.dname,b.loc
from emp a
left join dept b on a.deptno=b.deptno
left join salgrade s
on (a.sal+IFNULL(a.comm, 0)) between s.losal and s.hisal;
NULL值字段相加还是NULL,用函数IFNULL(a.comm, 0)
【大数据零基础】SQL语法2
最新推荐文章于 2023-12-02 13:08:47 发布