求助：顺丰大数据笔试题

题目一、

现在有三张数据表，一张为学生信息表students，一张为学生分数表score，还有一张为年级课程表class，表结构如下：

1.用一条sql查询出每个年级参加每门科目考试的总人数及平均成绩。

2.用一条sql查询出每个年级参加了所有科目考试的学生的总人数。

3.用一条sql查询2年级参加了所有科目考试并且每门课程的成绩都在80分以上的学生信息。

题目二、

有一张学生信息表，结构如下：

用一条sql删除除了id不同，其它信息完全相同的冗余信息

题目三、

题目四、

有一个表记录一个网点所有快递到达和离开的时间，如果离开时间不等于到达时间则代表这个运单在这个网点有停留，如何统计出在2018-03-01 13:00到2018-03-01 15：00之间在该网点有停留的运单数

Waybillno     arr_time                leave_time

1            2018-03-01 12:40:10      2018-03-01 13:50:10

2            2018-03-01 11:29:45      2018-03-01 12:40:18

3            2018-03-01 14:35:10      2018-03-01 16:00:00

第四题 select count(*) from tab where arr_time <>leave_time  and  (arr_time between 2018-03-01 13:00  and 2018-03-01 15：00) and   (leave_time  between 2018-03-01 13:00  and 2018-03-01 15：00)

SELECT * FROM CLASS_221; --年级表
SELECT * FROM Stu_221;--学生表
select * from SCORE_221;-- 成绩表
--1. 用一条sql查询出每个年级参加每门科目考试的总人数及平均成绩。
SELECT T.年级,C.课程,COUNT(1),AVG(S.分数) --SELECT *
FROM Stu_221 T
INNER JOIN SCORE_221 S ON T.学号 = S.学号 INNER JOIN CLASS_221 C ON C.年级 = T.年级 AND C.课程 = S.课程
GROUP BY T.年级,C.课程;

--2. 用一条sql查询出每个年级参加了所有科目考试的学生的总人数。
--先确定这些学生是否参加了所有科目考试
--方法一
SELECT A.年级,SUM(1) FROM
(SELECT T.年级,S.学号,COUNT(S.课程) K FROM Stu_221 T INNER JOIN SCORE_221 S ON T.学号 = S.学号 GROUP BY T.年级,S.学号) A
INNER JOIN
(SELECT C.年级,COUNT(C.课程) K FROM CLASS_221 C GROUP BY C.年级) B ON A.年级 = B.年级 AND A.K = B.K
GROUP BY A.年级;

--方法二

SELECT CASE WHEN C.年级 = T.年级 AND T.学号 = S.学号 AND S.课程 = 课程 THEN 1 ELSE END

SELECT *
FROM CLASS_221 C
INNER JOIN Stu_221 T ON C.年级 = T.年级 LEFT JOIN SCORE_221 S ON T.学号 = S.学号 AND C.课程 = S.课程
GROUP BY T.年级,S.学号;

SELECT C.,T.,S.*,ROW_NUMBER()OVER(PARTITION BY c.年级,S.课程 ORDER BY C.年级) a FROM CLASS_221 C
INNER JOIN Stu_221 T ON C.年级 = T.年级 LEFT JOIN SCORE_221 S ON T.学号 = S.学号 AND C.课程 = S.课程

--3. 用一条sql查询2年级参加了所有科目考试并且每门课程的成绩都在80分以上的学生信息。
SELECT * FROM Stu_221 ST WHERE ST.学号 IN
(SELECT A.学号 FROM
(SELECT T.年级,S.学号,COUNT(S.课程) K FROM Stu_221 T INNER JOIN SCORE_221 S ON T.学号 = S.学号
WHERE S.分数>80 GROUP BY T.年级,S.学号) A
INNER JOIN
(SELECT C.年级,COUNT(C.课程) K FROM CLASS_221 C GROUP BY C.年级) B ON A.年级 = B.年级 AND A.K = B.K)

--用一条sql语句提取出每个人的所在的城市信息，结果如下：
select * from zone;
--方法一
SELECT Z.NAME,
SUBSTR(Z.ADDRESS,INSTR(Z.ADDRESS,'-',1,1)+1,INSTR(Z.ADDRESS,'-',1,2)-INSTR(Z.ADDRESS,'-',1,1)-1) AS CITY
FROM zone Z;

题目四、
SELECT SUM(CASE WHEN A.ARR_TIME < TO_DATE('2018030113','YYYYMMDDHH24')
AND A.LEAVE_TIME > TO_DATE('2018030113','YYYYMMDDHH24') THEN 1
WHEN A.ARR_TIME > TO_DATE('2018030113','YYYYMMDDHH24')
AND A.ARR_TIME < TO_DATE('2018030115','YYYYMMDDHH24') THEN 1
ELSE 0 END) AS SUM_D
FROM DD A
WHERE A.ARR_TIME <> A.LEAVE_TIME

你这是什么意思？帮你做所有答案吗？

那你应该只说3,4题

1.1  select nianji,kecheng,count(*) as renshu,avg(fenshu) as pingjunfen from (
select a.xuehao,a.nianji,b.kecheng,b.fenshu from students a
left join score b on a.xuehao =b.xuehao
left join class c on a.nianji =c.nianji and b.kecheng =c.kecheng
)a
group by nianji,kecheng

第三题  select name ,SUBSTRING(city,1,POSITION('-'  IN city)-1)as city from (select  name ,SUBSTRING(city,POSITION('-'  IN city)+1,length(city))as city from name)a

您好，我是有问必答小助手，你的问题已经有小伙伴为您解答了问题，您看下是否解决了您的问题，可以追评进行沟通哦~

如果有您比较满意的答案 / 帮您提供解决思路的答案，可以点击【采纳】按钮，给回答的小伙伴一些鼓励哦～～

ps:问答VIP仅需29元，即可享受5次/月 有问必答服务，了解详情>>>https://vip.csdn.net/askvip?utm_source=1146287632

非常感谢您使用有问必答服务，为了后续更快速的帮您解决问题，现诚邀您参与有问必答体验反馈。您的建议将会运用到我们的产品优化中，希望能得到您的支持与协助！

速戳参与调研>>>https://t.csdnimg.cn/Kf0y