题目描述
获取所有部门中当前员工薪水最高的相关信息,给出dept_no, emp_no以及其对应的salary
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 `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`));
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 `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 | 10001 | 88958 |
d002 | 10006 | 43311 |
d003 | 10005 | 94692 |
d004 | 10004 | 74057 |
d005 | 10007 | 88070 |
d006 | 10009 | 95409 |
select d.dept_no,e.emp_no,max(e.salary) as salary
from salaries as e inner join dept_emp as d
on d.emp_no = e.emp_no
where d.to_date = '9999-01-01' and e.to_date = '9999-01-01'
group by d.dept_no;
解释:
用GROUP BY d.dept_no将每个部门分为一组,用MAX()函数选取每组中工资最高者
select d.dept_no , d.emp_no,s.salary
from dept_emp as d
join salaries as s
on d.emp_no=s.emp_no
where d.to_date=
'9999-01-01'
and s.to_date=
'9999-01-01'
group by d.dept_no
having max(s.salary);
SQL中的group by 函数:
SQL GROUP BY 子句与 SELECT 语句结合在一起使用,可以将相同数据分成一组。
在 SELECT 语句中,GROUP BY 子句紧随 WHERE 子句,在 ORDER BY 子句之前。
语法:
GROUP BY 子句的基本语法如下所示。GROUP BY 子句必须在 WHERE 子句的条件之后,ORDER BY 子句(如果有的话)之前。
SELECT column1, column2
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2
ORDER BY column1, column2
示例:
考虑含有如下所示记录的 CUSTOMERS 表:
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
如果你想要知道每个客户的薪水如何,可以写一个带有 GROUP BY 子句的查询:
SQL> SELECT NAME, SUM(SALARY) FROM CUSTOMERS
GROUP BY NAME;
结果如下所示:
+----------+-------------+
| NAME | SUM(SALARY) |
+----------+-------------+
| Chaitali | 6500.00 |
| Hardik | 8500.00 |
| kaushik | 2000.00 |
| Khilan | 1500.00 |
| Komal | 4500.00 |
| Muffy | 10000.00 |
| Ramesh | 2000.00 |
+----------+-------------+
现在,让我们换一张 CUSTOMERS 表,表中记录的 NAME 字段有重复值:
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Ramesh | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | kaushik | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
同样,如果你想要知道每个客户的薪水如何的话,可以写一个带有 GROUP BY 子句的查询:
SQL> SELECT NAME, SUM(SALARY) FROM CUSTOMERS
GROUP BY NAME;
结果如下所示:
+---------+-------------+
| NAME | SUM(SALARY) |
+---------+-------------+
| Hardik | 8500.00 |
| kaushik | 8500.00 |
| Komal | 4500.00 |
| Muffy | 10000.00 |
| Ramesh | 3500.00 |
+---------+-------------+