练习（1）

题目1：

好评率是用户对产品评价的重要指标。现在需要统计2019年3月1日到2019年3月31日，用户'小张'提交的"母婴"类目"DW"品牌的好评率（好评率=“好评”评价量/总评价量），请写出SQL/Python/其他语言查询语句:
用户评价详情表：a
字段：id（评价id，主键），create_time（评价创建时间，格式'2019-01-01'）， user_name(用户名称)，goods_id(商品id，外键) ，
sub_time（评价提交时间，格式'2019-01-01 23:10:32'），sat_name（好评率类型，包含：“好评”、“中评”、“差评”）

商品详情表：b
字段：goods_id（商品id，主键），goods_name（商品类目）, brand_name(品牌名称)

参考答案：

select count(case when sat_name='好评' then 1 else 0 end )/count(1) from

(select goods_id,sat_name

from a

where user_name='小张'

and create_time>='2019-03-01'

and create_time<='2019-03-31'

join (select goods_id

from b

where goods_name='母婴'

and brand_name='DW'

)b on a.goods_id=b.goods_id

思路二：

select

sum(case when sat_name = '好评' then 1 else 0 end)/sum(case when sat_name is not null then 1 else 0 end) as 好评率

from a join b on a.goods_id = b.goods_id

where a.user_name = '小张'

and goods_name = '母婴'

and brand_name = 'DW'

and create_time between '2019-03-01' and '2019-03-31'

题目2：

编写一个 SQL 语句，来删除 contacts 表中所有重复的姓名，重复的姓名里只保留 id 最小的那个。

delete from contacts

where id not in (select id from (select min(id) as id from contacts group by name)a)

题目3：

online_class_situations 表展示了一些同学上网课的行为活动。
每行数据记录了一名同学在退出网课之前，当天使用同一台设备登录课程后听过的课程数目（可能是0个）。
写一条 SQL 语句，查询每位同学第一次登录平台听课的设备ID (device_id)。

表定义: online_class_situations (网课上课情况表)

列名	类型	注释
student_id	int	学生 id
device_id	int	设备 id
date	date	课程的上课日期
course_number	int	课程数量

表的主键是 (student_id, date) 联合主键

select student_id,device_id from online_class_situations ocs

where (student_id,date) in(

select student_id,min(date) as date from online_class_situations

where course_number > 0

group by student_id

)

题目4：

有一个员工表dept_emp简况如下:

emp_no	dept_no	from_date	to_date
10001	d001	1986-06-26	9999-01-01
10002	d001	1996-08-03	9999-01-01
10003	d002	1995-12-03	9999-01-01

第一行表示为员工编号为10001的部门是d001部门。

有一个部门经理表dept_manager简况如下:

dept_no	emp_no	from_date	to_date
d001	10002	1996-08-03	9999-01-01
d002	10003	1990-08-05	9999-01-01

第一行表示为d001部门的经理是编号为10002的员工。

获取所有的员工和员工对应的经理，如果员工本身是经理的话则不显示，以上例子如下:

emp_no	manager
10001	10002

10002和10003本身都是经理

#方法一

#select de.emp_no as emp_no,dm.emp_no as manager

#from dept_emp de,dept_manager dm

#where de.dept_no = dm.dept_no

#and de.emp_no != dm.emp_no

#方法二

#select de.emp_no as emp_no,dm.emp_no as manager

#from dept_emp de,dept_manager dm

#where de.dept_no = dm.dept_no

#having emp_no != manager

#方法三

select e.emp_no,m.emp_no

from dept_emp as e

inner join dept_manager as m

on e.dept_no=m.dept_no

where e.emp_no!=m.emp_no

题目5：有一个员工表dept_emp简况如下:

emp_no	dept_no	from_date	to_date
10001	d001	1986-06-26	9999-01-01
10002	d001	1996-08-03	9999-01-01
10003	d002	1996-08-03	9999-01-01

有一个薪水表salaries简况如下:

emp_no	salary	from_date	to_date
10001	88958	2002-06-22	9999-01-01
10002	72527	2001-08-02	9999-01-01
10003	92527	2001-08-02	9999-01-01

获取每个部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary，按照部门编号dept_no升序排列，以上例子输出如下:

dept_no	emp_no	maxSalary
d001	10001	88958
d002	10003	92527

#思路一

#select t.dept_no,t.emp_no,t.salary from (

#select dept_no,de.emp_no as emp_no,salary,dense_rank()over (partition by dept_no order by salary desc) ranking

#from dept_emp de,salaries s

#where de.emp_no = s.emp_no

#group by dept_no 加了group by错误原因

#) t

#where ranking = 1

#order by dept_no

#方法一

#select t.dept_no,t.emp_no,t.salary from

#select

# de.dept_no,de.emp_no,s.salary,

# row_number() over(partition by de.dept_no order by s.salary desc) as rk

#from dept_emp de

# inner join

# salaries s

# on de.emp_no=s.emp_no

#)t

#where t.rk=1;

#方法二

#SELECT d1.dept_no, d1.emp_no, s1.salary

#FROM dept_emp as d1

#INNER JOIN salaries as s1

#ON d1.emp_no=s1.emp_no

#AND d1.to_date='9999-01-01' #表示在职员工如果表中都是在职，可以不加此条件

#AND s1.to_date='9999-01-01'

#WHERE s1.salary in (SELECT MAX(s2.salary)

#FROM dept_emp as d2

#INNER JOIN salaries as s2

#ON d2.emp_no=s2.emp_no

#AND d2.to_date='9999-01-01'

#AND s2.to_date='9999-01-01'

#AND d2.dept_no = d1.dept_no

#ORDER BY d1.dept_no;

#方法三

SELECT t.dept_no,t.emp_no,t.salary AS maxSalary

FROM (SELECT d.dept_no,d.emp_no,s.salary,DENSE_RANK() OVER (PARTITION BY d.dept_no ORDER BY s.salary DESC) AS sal_rank

FROM dept_emp d

INNER JOIN salaries s

ON d.emp_no = s.emp_no) t

WHERE t.sal_rank = 1

ORDER BY t.dept_no;

方法四

先将每个部门最高工资和对应的部门找出来，形成表t2；
再将每个人对应的的工资及对应的部门找出来形成表t1；
再将t1和t2连接，条件是部门相同，工资相同。

代码：

select t2.dept_no,t1.emp_no,t2.maxSalary

from

(select s.emp_no,d.dept_no,s.salary

from dept_emp as d

join salaries as s

on d.emp_no = s.emp_no

) as t1

join (

select d.dept_no,max(salary) as maxSalary

from dept_emp as d

join salaries as s

on d.emp_no = s.emp_no

group by d.dept_no) as t2

on t1.dept_no = t2.dept_no and t1.salary = t2.maxSalary

-- 应付题目要求，使用排序

order by t2.dept_no

方法五

首先也是将每个人和工资、部门联系起来，然后使用where ；
条件是(部门，工资) in 子查询（部门，最高工资）

代码：

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.dept_no,s.salary) in (

-- in 的用法可以使用元组形式

-- 找出部门和对应的最高工资

select d.dept_no,max(s.salary)

from dept_emp as d

join salaries as s

on d.emp_no = s.emp_no

group by d.dept_no

)

-- 应付题目要求，排序输出

order by d.dept_no

注：in的方法可以使用元组式的，也就是(字段1，字段2) in select ... （相关子查询）

题目6：

有一个员工表employees简况如下:

emp_no	birth_date	first_name	last_name	gender	hire_date
10001	1953-09-02	Georgi	Facello	M	1986-06-26
10002	1964-06-02	Bezalel	Simmel	F	1996-08-03

有一个，部门员工关系表dept_emp简况如下:

emp_no	dept_no	from_date	to_date
10001	d001	1986-06-26	9999-01-01
10002	d001	1996-08-03	9999-01-01

有一个部门经理表dept_manager简况如下:

dept_no	emp_no	from_date	to_date
d001	10002	1996-08-03	9999-01-01

有一个薪水表salaries简况如下:

emp_no	salary	from_date	to_date
10001	88958	1986-06-26	9999-01-01
10002	72527	1996-08-03	9999-01-01

获取所有非manager员工薪水情况，给出dept_no、emp_no以及salary，以上例子输出:

dept_no	emp_no	salary
d001	10001	88958

思路一：

select a.dept_no,a.emp_no,b.salary from dept_emp a

join salaries b

on a.emp_no = b.emp_no

where (a.dept_no,a.emp_no) not in (select dept_no,emp_no from dept_manager)

思路二：

看了好多解法都是用NOT IN 的，我就说说我这个没用NOT IN 的解法吧。

首先还是employees表和dept_emp员工部门表连接，找到员工和部门的关系；

接着连接dept_manager表，注意这里连接条件部门id即dept_no要相等；但是员工id即emp_no要不相等。emp_no不相等的就是非manager员工。

最后连接salaries表，按emp_no连接，就找到每个员工对应的工资；别忘了筛选条件是当前日期！

完整的代码就是：

SELECT d.dept_no, d.emp_no, s.salary FROM employees e

JOIN dept_emp d ON e.emp_no = d.emp_no

JOIN dept_manager d1 ON d.dept_no = d1.dept_no AND d.emp_no <> d1.emp_no

JOIN salaries s ON d.emp_no = s.emp_no

WHERE s.to_date = '9999-01-01' ORDER BY e.emp_no;

思路三：

思路：本题的重点在于获取非manager员工，因此我们使用inner join将employees,dept_emp,salaries表进行连接，在 where 条件中加入

1	e.emp_no not in (select emp_no from dept_manager where to_date = '9999-01-01')

来进行判断，由此来排除掉是manager的员工。

select de.dept_no, e.emp_no, s.salary

from employees e, dept_emp de, salaries s

where e.emp_no = de.emp_no and e.emp_no = s.emp_no

and e.emp_no not in (select emp_no from dept_manager where to_date = '9999-01-01')

思路四：

select de.dept_no, de.emp_no, sa.salary

from salaries as sa

join dept_emp as de on de.emp_no = sa.emp_no

left join dept_manager as dm on dm.emp_no = de.emp_no

where dm.emp_no is null

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

题目7：有一个，部门关系表dept_emp简况如下:

emp_no	dept_no	from_date	to_date
10001	d001	1986-06-26	9999-01-01
10002	d001	1996-08-03	9999-01-01

有一个部门经理表dept_manager简况如下:

dept_no	emp_no	from_date	to_date
d001	10002	1996-08-03	9999-01-01

有一个薪水表salaries简况如下:

emp_no	salary	from_date	to_date
10001	88958	2002-06-22	9999-01-01
10002	72527	1996-08-03	9999-01-01

获取员工其当前的薪水比其manager当前薪水还高的相关信息，

第一列给出员工的emp_no，
第二列给出其manager的manager_no，
第三列给出该员工当前的薪水emp_salary,
第四列给该员工对应的manager当前的薪水manager_salary

以上例子输出如下:

emp_no	manager_no	emp_salary	manager_salary
10001	10002	88958	72527

思路一：

select t1.emp_no,t2.emp_no as manager_no,t1.salary as emp_salary,t2.salary as manager_salary

from

(

select a.emp_no,c.salary,a.dept_no from

dept_emp a,dept_manager b,salaries c

where a.dept_no = b.dept_no

and a.emp_no !=b.emp_no

and a.emp_no = c.emp_no

) t1,

(

select d.dept_no,d.emp_no,e.salary

from dept_manager d,salaries e

where d.emp_no = e.emp_no

) t2

where t1.dept_no = t2.dept_no

and t1.salary >= t2.salary

思路二：

select de.emp_no,dm.emp_no as manager_no,

s1.salary as emp_salary,s2.salary as manager_salary

from dept_emp de,dept_manager dm,salaries s1,salaries s2

where de.dept_no=dm.dept_no

and de.emp_no=s1.emp_no

and dm.emp_no=s2.emp_no

and s1.salary>s2.salary

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

and s1.to_date='9999-01-01';

思路三：

法一：一表多用

select de.emp_no,

dm.emp_no as manager_no,

s1.salary as emp_salary,

s2.salary as manager_salary

from dept_emp de,dept_manager dm,salaries s1,salaries s2

where de.dept_no=dm.dept_no

and de.emp_no=s1.emp_no

and dm.emp_no=s2.emp_no

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

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

and s1.salary>s2.salary

法二：分别构建员工工资表和manager工资表，再用部门号联结

员工工资表

SELECT de.dept_no, de.emp_no, s.salary AS emp_salary

FROM dept_emp de, salaries s

WHERE de.emp_no=s.emp_no

AND s.to_date='9999-01-01'

manager工资表

SELECT dm.dept_no, dm.emp_no AS manager_no, s.salary AS manager_salary

FROM dept_manager dm, salaries s

WHERE dm.emp_no=s.emp_no

AND s.to_date='9999-01-01'

最终SQL

SELECT a.emp_no, b.manager_no, a.emp_salary, b.manager_salary

FROM

(SELECT de.dept_no, de.emp_no, s.salary AS emp_salary

FROM dept_emp de, salaries s

WHERE de.emp_no=s.emp_no

AND s.to_date='9999-01-01') a,

(SELECT dm.dept_no, dm.emp_no AS manager_no, s.salary AS manager_salary

FROM dept_manager dm, salaries s

WHERE dm.emp_no=s.emp_no

AND s.to_date='9999-01-01') b

WHERE a.dept_no=b.dept_no

AND a.emp_salary>b.manager_salary;

思路四：

只用自然连接就可以做出来

select

a.emp_no,
b.emp_no,
c.salary,
d.salary
from
dept_emp a,
dept_manager b,
salaries c,
salaries d
where
a.dept_no=b.dept_no
and a.emp_no<>b.emp_no
and a.emp_no=c.emp_no
and b.emp_no=d.emp_no
and c.salary>d.salary;

posted @ 2022-08-06 17:53 萧六弟阅读(44) 评论(0) 收藏举报

刷新页面返回顶部

戴小帅的学习笔记

练习（1）

方法四

代码：

方法五

代码：

法一：一表多用

法二：分别构建员工工资表和manager工资表，再用部门号联结

公告