MySQL笔试题01

MySQL用的不熟，不知道正确与否，做个笔记
参考：
分组排序：https://www.pianshen.com/article/14531420134/
https://blog.csdn.net/weixin_38450840/article/details/88836170
行转列：http://www.360doc.com/content/18/0525/20/14808334_757019563.shtml
建表SQL：https://pan.baidu.com/s/1WJSAnLIAZhpo2ZuOaOOAlA 提取码：hyp9

• 请按题目要求编写sql语句并能在MySQL5.7 的版本上执行通过,不能使用如rank () over 或 dense_rank () over 等窗口函数；

  • 项目基本信息表tbproject，用于记录了项目的基本信息。

    数据库字段	中文名称	备注
    project_no	项目编号	主键
    project_name	项目名称	非空
    depart_no	项目所属部门	非空；关联tbdepart
    signdate	合同签订时间	非空
    amount	合同金额	非空
    state_no	项目状态	非空

  • 部门信息表tbdepart，用于记录部门的基本信息。

    数据库字段	中文名称	备注
    depart_no	部门编号	主键
    depart_name	部门名称	非空

  • 人员信息表tbuser，用于记录人员的基本信息。

    数据库字段	中文名称	备注
    user_no	人员编号	主键
    user_name	姓名	非空
    depart_no	部门编号	非空；关联tbdepart

  • 项目工作量记录表tbworkload，用于记录项目参与人员所用工作量信息。

    数据库字段	中文名称	备注
    project_no	项目编号	主键；关联tbproject
    user_no	参与人员编号	主键；关联tbuser
    workload	工作量（天）	非空

1、查询各项目中工作量从高到低排名第一的员工信息。

• 输出【员工编号user_no、项目编号project_no、工作量workload】
• 需考虑第一名重复的情况

# 第一种,缺点是limit后面的数字对查询结果有限制 （考虑重复的话，不符合题意）
SELECT t.*
FROM (SELECT * 
		FROM tbworkload 
       ORDER BY workload DESC LIMIT 10 ) t 
GROUP BY project_no;
# 第二种，缺点是数据量大时效率低
select * 
  from tbworkload w 
 where (w.project_no,w.workload) in
		(select p.project_no , max(p.workload) 
           from tbworkload p 
          group by p.project_no);
# 第三种，与第二种类似
SELECT t.* 
FROM tbworkload t 
INNER JOIN (SELECT t1.project_no, MAX(t1.workload) AS workload 
              FROM tbworkload t1 
             GROUP BY t1.project_no) t2 ON t.project_no = t2.project_no 
              						   AND t.workload = t2.workload;

2、查询所有项目中工作量之和从高到低排名第三的那个项目信息。

• 输出【项目编号project_no、项目名称project_name、总工作量total】
• 不考虑工作量相同的情况

select w.project_no,p.project_name,sum(workload) total 
  from tbworkload w 
  left join tbproject p on p.project_no = w.project_no
 group by w.project_no 
 order by total desc 
 limit 2,1; 

3、筛选出签订合同时间在2019年、项目状态为“2”、合同金额从高到低排名第二名的项目信息。

• 输出【项目名称project_name、合同金额amount】

• 需考虑合同金额相同的情况。合同金额相同则排名相同，排名顺序不跳跃

select p.project_name,p.amount
  from tbproject p
 where date_format(p.signdate, '%Y') = date_format('2019-01-01', '%Y') 
   and p.state_no = '2'
   and p.amount =
	(select amount
	  from tbproject
	 where date_format(signdate, '%Y') = date_format('2019-01-01', '%Y') 
	   and state_no = '2' 
	 order by amount desc 
	 limit 1,1);

4、统计各部门项目中人均工作量workload大于80天的项目个数

• 输出【部门编号depart_no、项目个数count】

select p.depart_no, count(p.project_no) count
  from tbproject p
 where p.project_no in 
	   (select project_no
		  from tbworkload w
		 group by w.project_no
		having avg(w.workload) > 80)
 group by p.depart_no;

5、将工作量表tbworkload中部门编号等于“400”的项目使用sql查询将行转列为下表结构（可假设人员固定）

项目编号	员工410	员工411	员工412
项目007	80	90	99
项目008	0	80	70
项目009	80	0	80

SELECT project_no '项目编号',  
		MAX(  
			CASE user_no  
			WHEN '员工410' THEN  
				workload
			else 0
			END  
		) '员工410',  
		MAX(  
			CASE user_no  
			WHEN '员工411' THEN  
				workload  
			else 0
			END  
		) '员工411',
		MAX(  
			CASE user_no  
			WHEN '员工412' THEN  
				workload  
			else 0
			END  
		) '员工412'
  FROM tbworkload w
 WHERE exists (select 1
				 from tbproject p
                where p.project_no = w.project_no
                  and p.depart_no = '400')
 GROUP BY project_no;