日期分组
-- select * from test;
-- select datediff(datetime,"2018-6-1") from test; ——0,1,2,3,4,5,6,7,8,9,10,11,12,13
-- select floor( datediff(datetime,"2018-6-1") /7) from test; ——0,0,0,0,0,0,0,1,1,1,1,1,1,1
-- select floor(datediff(datetime,"2018-6-1") /7)*7 from test; ——0,0,0,0,0,0,0,7,7,7,7,7,7,7
1、确定开始时间 ——2018-6-1~2018-6-8
-- select date_add("2018-6-1",interval floor(datediff(datetime,"2018-6-1") /7)*7 day ) from test;
2、确定结尾时间 ——2018-6-7~2018-6-14
-- select date_add("2018-6-1",interval floor(datediff(datetime,"2018-6-1") /7)*7 +6 day ) from test;
3、拼接开始时间和结尾时间
select concat (
date_add("2018-6-1",interval floor(datediff(datetime,"2018-6-1") /7)*7 day )
,"~"
,date_add("2018-6-1",interval floor(datediff(datetime,"2018-6-1") /7)*7 +6 day )
)as time from test;
员工部门
-- 10.列出与“SCOTT”从事相同工作的所有员工。
-- select job from emp where ename='SCOTT';
-- select * from emp where job=(select job from emp where ename='SCOTT');
-- 11.列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。
-- select sal from emp where deptno=30;--set
-- select ename,sal from emp where sal in (select sal from emp where deptno=30) and deptno<>30;
-- 12.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。
-- select max(sal) from emp where deptno=30;
-- select ename,sal from emp where sal>(select max(sal) from emp where deptno=30) and deptno<>30;
-- 13.列出在每个部门工作的员工数量、平均工资和平均服务期限。
-- select count(ename),avg(sal+if(comm is null,0,comm)),avg(datediff("2021-11-10",hiredate)) from emp;
-- 15.列出所有部门的详细信息和部门人数。
-- select count(*),deptno from emp group by deptno;
-- select dept.*,c from dept
-- inner join (select count(*)as c,deptno from emp group by deptno)as e
-- on dept.deptno=e.deptno;
-- 17.列出各个部门的MANAGER(经理)的最低薪金。
Top1(部门分组,经理薪金最低的)
-- select * from emp as e1 where 0=(select count(*) from emp as e2
-- where e1.deptno=e2.deptno and e1.sal>e2.sal);
group by 写法
-- select * from emp where job='MANAGER'
-- select min(sal) as s,deptno from
-- (select * from emp where job='MANAGER') as e group by deptno
-- select * from (select * from emp where job='MANAGER') as e1
-- INNER JOIN (select min(sal) as s,deptno from
-- (select * from emp where job='MANAGER') as e group by deptno) as e2
-- on e1.deptno=e2.deptno
-- where sal=s;
-- 18.列出所有员工的年工资,按年薪从低到高排序。
-- select ename,((sal+if(comm is null,0,comm))*12)as end from emp order by end desc;
-- 思考: 列出每个部门薪水前两名最高的人员名称以及薪水
Top2
-- select * from emp as e1 where 2>(select count(*) from emp as e2
-- where e1.deptno=e2.deptno and e1.sal>e2.sal);