mysql学习--项目汇总

项目十 行程和用户

题目意思本身很简单，难点在于如何同时计算取消的数量和总数量。 总数量很简单，就是COUNT(*)， 同时计算取消数量可以用SUM(CASE END)来实现。对status列计算数量，如果是cancel就记1，complete记为0. P.S. 如果需要userID 匹配 clientID和driverID两列，需要写两个Lef Join。 但是可以通过CASE ...END 在最终结果只显示一列。

项目十一 各部门工资第三高的员工

思路一 因为只有两个部门，我们可以取巧分别对每个部门按工资降序排名，取前三行，然后UNION。 需要注意的是，ORDER BY 和 LIMIT本身不支持在子查询中使用。所以需要加上括号形成独立的几个表而不是UNION的子查询。

思路二 也是大家普遍在网上搜到的答案。 和昨天分数排名的思想类似，用到了辅助表。 emp1是我们的基础表，emp2是辅助表。 将emp1里的每个salary和整张emp2比较。下面来捋下过程。 以IT部门为例，emp1的salary有 6.9万，7万，8.5万，9万四个数 ① emp1工资是6.9万的时候，emp2表里的 count是3，说明有三个大于它的（间接说明它是第四大） ② emp1工资是7万的时候，emp2表里的count是2，说明有两个大于它的（间接说明它是第三大） ③emp1工资是8.5万的时候，emp2表里的count是1，说明有1个大于它的（间接说明它是第二大） ④emp1工资是9万的时候，emp2表里的count是0，说明没有大于它的（间接说明它是最大的） 在code里就是emp2.Salary > （emp1.Salary =6.9） 然后我们要求的是前三大，所以是COUNT() < 3。 

 

项目十二 分数排名

不连续 和昨天的类似，有个小改动。

项目七 各部门工资最高的

这题思路比较简单，就是先查询出每个部门最高的工资，然后再通过JOIN匹配部门信息 

项目八 换座位

从最后结果来看就是 ： ①id为偶数的需要往前挪 ②id为奇数的需要往后挪 ③再考虑最后一位是奇数还是偶数，奇数不变（发现偶数的情况已经包含在前面了） 通过 id%2=1 来判断是奇数。 这里需要注意的地方是，最后一位，我们在判断奇数往后挪的时候，是不包含最后一位的。所以在②的时候要限定 id 小于总个数。 

项目九 分数排名

这里的排名是连续排名。将分数去重后的清单排名就是最终排名。然后将这个排名JOIN原始数据。 问题在于，怎么在去重的分数清单增加一列ID，这个ID会随着数据自动增加。这个可以通过参数化查询搞定，但是我们还没学，所以pass。 然后就有个取巧的办法。我们还是需要获取去重的分数清单，但是不需要排名ID了： ① 原始表里的最大值。 分数清单只取最大值，然后COUNT(*)，这样就只有1 ② 原始表里的第二大值。 分数清单取最大值和第二大值，然后COUNT(*), 这样就是2 ``` 以此内推。 在code里就是 分数清单里的score要大于等于原始表的某个具体的值。 这个写法的缺点是，每一个原始表socre列的值，都需要重新扫描去重后的分数清单，在数据表大的情况下，性能会很差。 

 任务四作业参考答案

项目五：组合两张表 （难度：简单）

在数据库中创建表1和表2，并各插入三行数据（自己造） 表1: Person +-------------+---------+ | 列名 | 类型 | +-------------+---------+ | PersonId | int | | FirstName | varchar | | LastName | varchar | +-------------+---------+ PersonId 是上表主键 表2: Address +-------------+---------+ | 列名 | 类型 | +-------------+---------+ | AddressId | int | | PersonId | int | | City | varchar | | State | varchar | +-------------+---------+ AddressId 是上表主键 编写一个 SQL 查询，满足条件：无论 person 是否有地址信息，都需要基于上述两表提供 person 的以下信息：FirstName, LastName, City, State

项目六：删除重复的邮箱（难度：简单）

编写一个 SQL 查询，来删除 email 表中所有重复的电子邮箱，重复的邮箱里只保留 Id 最小 的那个。 +----+---------+ | Id | Email | +----+---------+ | 1 | a@b.com | | 2 | c@d.com | | 3 | a@b.com | +----+---------+ Id 是这个表的主键。 例如，在运行你的查询语句之后，上面的 Email表应返回以下几行: +----+------------------+ | Id | Email | +----+------------------+ | 1 | a@b.com | | 2 | c@d.com | +----+------------------+ 

项目三：超过5名学生的课（难度：简单） 创建如下所示的courses 表 ，有: student (学生) 和 class (课程)。 例如,表: +---------+------------+ | student | class | +---------+------------+ | A | Math | | B | English | | C | Math | | D | Biology | | E | Math | | F | Computer | | G | Math | | H | Math | | I | Math | | A | Math | +---------+------------+ 编写一个 SQL 查询，列出所有超过或等于5名学生的课。 应该输出: +---------+ | class | +---------+ | Math | +---------+ Note: 学生在每个课中不应被重复计算。

项目四：交换工资（难度：简单） 创建一个 salary表，如下所示，有m=男性 和 f=女性的值 。 例如: | id | name | sex | salary | |----|------|-----|--------| | 1 | A | m | 2500 | | 2 | B | f | 1500 | | 3 | C | m | 5500 | | 4 | D | f | 500 | 交换所有的 f 和 m 值(例如，将所有 f 值更改为 m，反之亦然)。要求使用一个更新查询，并且没有中间临时表。 运行你所编写的查询语句之后，将会得到以下表: | id | name | sex | salary | |----|------|-----|--------| | 1 | A | f | 2500 | | 2 | B | m | 1500 | | 3 | C | f | 5500 | | 4 | D | m | 500