查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));输出:
emp_no t 10001 17 10004 16 10009 18
select emp_no,count(salary) t
from
(select distinct emp_no,salary
from salaries) a
group by emp_no
having t>15
解析:
这里的distinct是剔除salary重复的。具体distinct还有的用法有
1.只对一列操作
2.对多列操作,表示选取 多列都不重复的数据,相当于 多列拼接的记录 的整个一条记录 , 不重复的记录。
比如SELECT DISTINCT province, city是说province和city 都不重复的情况.
而distinct必须放在开头,且输出要求是多列,它会认为是多列的distinct,某个段还是重复
另外,关于主键重复的问题:当表中需要两个字段才能唯一确定一条记录,那么通常会把这两个字段设为主键,称为“复合主键”,对于复合主键的两列的任何一列,是可以重复的。
找出所有员工当前(to_date='9999-01-01')具体的薪水salary情况,对于相同的薪水只显示一次,并按照逆序显示
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));输出描述:
salary 94692 94409 88958 88070 74057 72527 59755 43311 25828
select distinct salary
from salaries
where to_date='9999-01-01'
order by salary desc;
解析:去重group by和distinct
group by语法可以根据给定数据列的每个成员对查询结果进行分组统计,最终得到一个分组汇总表。
SELECT子句中的列名必须为分组列或列函数。列函数对于GROUP BY子句定义的每个组各返回一个结果(不管它内部逻辑是怎样的)。如下,小黄有两个,但是
并且,针对group by返回一个这个特性,可以用来查询最高/最低
SELECT dept, MAX(salary) AS MAXIMUM
FROM sheet
GROUP BY dept
1.单纯的去重操作使用distinct,速度是快于group by的。distinct支持单列、多列的去重方式。
Group by 比 distinct效率高,distinct需要读取所有记录,而group by只需要读取分组的
group by 去重
数据分布 | 去重方式 | 原因 |
离散 | group | distinct空间占用较大,在时间复杂度允许的情况下,group 可以发挥空间复杂度优势 |
集中 | distinct | distinct空间占用较小,可以发挥时间复杂度优势 |
两个极端:
1.数据列的所有数据都一样,即去重计数的结果为1时,用distinct最佳
2.如果数据列唯一,没有相同数值,用group 最好
mysql获取当前时间 : now(),sqlServer获取当前时间: getDate()
获取所有部门当前manager的当前薪水情况,给出dept_no, emp_no以及salary,当前表示to_date='9999-01-01'
CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL,
`emp_no` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));输出描述:
dept_no emp_no salary d001 10002 72527 d004 10004 74057 d003 10005 94692 d002 10006 43311 d006 10010 94409
select d.dept_no,d.emp_no,s.salary
from dept_manager d inner join salaries s on d.emp_no=s.emp_no
where s.to_date='9999-01-01'and d.to_date='9999-01-01'
根据题意,要获取当前manager的当前salary情况,再加上限制条件d.to_date = '9999-01-01' AND s.to_date = '9999-01-01'即可(因为同一emp_no在salaries表中对应多条涨薪记录,而当s.to_date = '9999-01-01'时是该员工当前的薪水记录)
题目描述
获取所有非manager的员工emp_no
CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL,
`emp_no` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));输出描述:
emp_no 10001 10003 10007 10008 10009 10011
select emp_no
from employees
where emp_no not in (select emp_no from dept_manager )
获取所有员工当前的manager,如果当前的manager是自己的话结果不显示,当前表示to_date='9999-01-01'。
结果第一列给出当前员工的emp_no,第二列给出其manager对应的manager_no。
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL,
`emp_no` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
select e.emp_no,d.emp_no as manager_no
from dept_emp e inner join dept_manager d on e.dept_no=d.dept_no
where e.to_date='9999-01-01' and d.to_date='9999-01-01'and d.emp_no!=e.emp_no