sql习题之group by

查找薪水涨幅超过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`));

 

答案:

SELECT emp_no, COUNT(emp_no) AS t FROM salaries 

GROUP BY emp_no HAVING t > 15

此题应注意以下四点：
1、用COUNT()函数和GROUP BY语句可以统计同一emp_no值的记录条数
2、根据题意，输出的涨幅次数为t，故用AS语句将COUNT(emp_no)的值转换为t
3、由于WHERE后不可跟COUNT()函数，故用HAVING语句来限定t>15的条件
4、最后存在一个理解误区，涨幅超过15次，salaries中相应的记录数应该超过16（从第2条记录开始算作第1次涨幅），不过题目为了简单起见，将第1条记录当作第1次涨幅，所以令t>15即可
/**  注意： 严格来说，下一条salary高于本条才算涨幅，但本题只要出现了一条记录就算一次涨幅，salary相同可以理解为涨幅为0，salary变少理解为涨幅为负 **/

 

在介绍GROUP BY 和 HAVING 子句前，我们必需先讲讲sql语言中一种特殊的函数:聚合函数，例如SUM, COUNT, MAX, AVG等。
这些函数和其它函数的根本区别就是它们一般作用在多条记录上。 SELECT SUM(population) FROM bbc  
这里的SUM作用在所有返回记录的population字段上，结果就是该查询只返回一个结果，即所有国家的总人口数。 
通过使用GROUP BY 子句，可以让SUM 和 COUNT 这些函数对属于一组的数据起作用。当你指定 GROUP BY region 时， 
属于同一个region(地区)的一组数据将只能返回一行值，也就是说，表中所有除region(地区)外的字段，只能通过 SUM, COUNT等聚合函数运算后返回一个值。 
HAVING子句可以让我们筛选成组后的各组数据，WHERE子句在聚合前先筛选记录.也就是说作用在GROUP BY 子句和HAVING子句前. 
而 HAVING子句在聚合后对组记录进行筛选。 
让我们还是通过具体的实例来理解GROUP BY 和 HAVING 子句，还采用第三节介绍的bbc表。 
SQL实例: 
　　一、显示每个地区的总人口数和总面积: SELECT region, SUM(population), SUM(area) 
FROM bbc 
GROUP BY region  
　　先以region把返回记录分成多个组，这就是GROUP BY的字面含义。分完组后，然后用聚合函数对每组中的不同字段(一或多条记录)作运算。 
　　二、 显示每个地区的总人口数和总面积.仅显示那些面积超过1000000的地区。 SELECT region, SUM(population), SUM(area) 
FROM bbc 
GROUP BY region 
HAVING SUM(area)>1000000  
　　在这里，我们不能用where来筛选超过1000000的地区，因为表中不存在这样一条记录。 
　　相反，HAVING子句可以让我们筛选成组后的各组数据.

eg
获取所有部门中当前员工薪水最高的相关信息，给出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`));
答案:

SELECT d.dept_no, s.emp_no, MAX(s.salary) AS salary

FROM salaries AS s INNER JOIN dept_emp As d

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

 

或者:

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);

 

比较:

select title,count(title) as t from titles 
group by title having t >= 2 

select title,count(title) as t from titles 
group by title where t >= 2 

上面是对的,下面是错的.

 

 

链接：https://www.nowcoder.com/questionTerminal/c8652e9e5a354b879e2a244200f1eaae
来源：牛客网

统计出当前各个title类型对应的员工当前薪水对应的平均工资。结果给出title以及平均工资avg。
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 IF NOT EXISTS "titles" (
`emp_no` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`from_date` date NOT NULL,
`to_date` date DEFAULT NULL); 

 

 

为什么这里不能用having

select title,avg(salary) as avg

from titles join salaries

on titles.emp_no=salaries.emp_no

where titles.to_date='9999-01-01' 

and salaries.to_date='9999-01-01'

group by title

上面是对的，可是下面就错了：

select title,avg(salary) as avg

from titles join salaries

on titles.emp_no=salaries.emp_no

group by title

having titles.to_date='9999-01-01' 

and salaries.to_date='9999-01-01'

--------------------------------

“Where” 是一个约束声明，使用Where来约束来之数据库的数据，Where是在结果返回之前起作用的，且Where中不能使用聚合函数。

“Having”是一个过滤声明，是在查询返回结果集以后对查询结果进行的过滤操作，在Having中可以使用聚合函数。

select title,avg(salary) as avg from salaries,titles where salaries.emp_no = titles.emp_no and salaries.to_date='9999-01-01'
and titles.to_date='9999-01-01'
group by title ;正确

select title,avg(salary) as avg from salaries,titles where salaries.to_date='9999-01-01'
and titles.to_date='9999-01-01'
group by title  having salaries.emp_no = titles.emp_no  ;不正确,因为group by之后的表中只有 title，avg字段可以继续进行having处理。我是这样理解的，可能有不足之处。