当前位置：首页 > news >正文

Task06：秋招秘籍 A

news 2026/3/26 21:33:21

教程地址
1. 各部门工资最高的员工
- 1.1 Employee 表
- 1.2 Department 表
- 1.3 各部门工资最高的员工
2. 换座位
3. 分数排名
4. 连续出现的数字
5. 树节点
6. 至少有五名直接下属的经理
7. 查询回答率最高的问题
8. 各部门前3高工资的员工
9. 平面上最近距离
10. 行程和用户

教程地址

https://github.com/datawhalechina/wonderful-sql
https://gitee.com/datawhalechina/wonderful-sql

1. 各部门工资最高的员工

1.1 Employee 表

先建表

mysql> USE ztc;
Database changed
mysql> 
mysql> CREATE TABLE if NOT EXISTS Employee-> ( Id INTEGER PRIMARY KEY ,->   Name VARCHAR(10) ,->   Salary INTEGER  ,->   DepartmentId  INTEGER-> );
Query OK, 0 rows affected, 1 warning (0.00 sec)

然后加入数据

mysql> INSERT INTO Employee VALUES->   (1, 'Joe', '70000', 1),->   (2, 'Henry', '80000', 2),->   (3, 'Sam', '60000', 2),->   (4, 'Max', '90000', 1);
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0mysql> select * from Employee;
+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
|  1 | Joe   |  70000 |            1 |
|  2 | Henry |  80000 |            2 |
|  3 | Sam   |  60000 |            2 |
|  4 | Max   |  90000 |            1 |
+----+-------+--------+--------------+
4 rows in set (0.00 sec)

1.2 Department 表

mysql> CREATE TABLE IF NOT EXISTS Department-> ( Id INTEGER PRIMARY KEY ,->   Name VARCHAR(10)-> );
Query OK, 0 rows affected, 1 warning (0.01 sec)mysql> INSERT INTO Department VALUES->   (1, 'IT'),->   (2, 'Sales')-> ;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0mysql> SELECT * FROM Department;
+----+-------+
| Id | Name  |
+----+-------+
|  1 | IT    |
|  2 | Sales |
+----+-------+
2 rows in set (0.00 sec)

1.3 各部门工资最高的员工

mysql> SELECT (SELECT Name AS Department ->         FROM Department AS d ->         WHERE d.Id=e1.DepartmentId->         ) AS Department,->        e1.Name AS Employee,->        e1.Salary-> FROM Employee AS e1-> WHERE e1.Salary = (->     SELECT MAX(e2.Salary)->     FROM Employee AS e2->     WHERE e1.DepartmentId = e2.DepartmentId->     GROUP BY e2.DepartmentId->   );
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| Sales      | Henry    |  80000 |
| IT         | Max      |  90000 |
+------------+----------+--------+
2 rows in set (0.00 sec)

确实还挺不好搞的

我想的得先拿到各个部门工资最高的人

那这个简单，直接 group，然后求 max，这样得到的是一个高工资的列表

我原本想直接用 in，凡是工资在这个列表里面的，都给展示出来

后来想想不对，如果一个人在他的部门工资第二，但是其他部门的第一

那么这个人的数据就也会显示出来，但是这是不应该显示的

那然后想到的就是，关联子查询，返回一个带有映射关系的列表

内查询分组求最大，然后按照组别作为关键词传出到外查询

我发现这个分组关键词，和 where 关键词，在关联子查询当中是一致的

好吧，总之在这个时候，我们对 Employee 这个单表完成的处理

呃，是的，我们其实最后才开始需要联表这一类的操作，刚开始是可以分析的

我们需要的是根据类别分组，然后求工资最大，这个两个都在 Employee 表

也就是说我们刚开始只需要处理单个，后面把类别的序号换为名称就好

那么对于结果表，我们同样使用一个子查询来进行序号和名称的转换

应该还是有另外的思路，比如说先内连接合成大表，然后再进行操作

2. 换座位

还是得先建表

mysql> CREATE TABLE IF NOT EXISTS seat-> ( Id INTEGER PRIMARY KEY ,->   Name VARCHAR(10)-> );
Query OK, 0 rows affected (0.05 sec)mysql> 
mysql> INSERT INTO seat VALUES->   (1, 'Abbot'),->   (2, 'Doris'),->   (3, 'Emerson'),->   (4, 'Green'),->   (5, 'Jeames')-> ;
Query OK, 5 rows affected (0.02 sec)
Records: 5  Duplicates: 0  Warnings: 0mysql> 
mysql> SELECT * FROM seat;
+----+---------+
| Id | Name    |
+----+---------+
|  1 | Abbot   |
|  2 | Doris   |
|  3 | Emerson |
|  4 | Green   |
|  5 | Jeames  |
+----+---------+
5 rows in set (0.00 sec)

然后来看看结果，总感觉我的实现过程有点奇妙：

mysql> SELECT ->   ROW_NUMBER()->     OVER (->       ORDER BY (->         CASE WHEN Id MOD 2 = 0 ->           THEN Id-1 ->           ELSE Id+1 ->         END)->     ) AS Id,->   Name-> FROM seat-> ORDER BY Id-> ;
+----+---------+
| Id | Name    |
+----+---------+
|  1 | Doris   |
|  2 | Abbot   |
|  3 | Green   |
|  4 | Emerson |
|  5 | Jeames  |
+----+---------+
5 rows in set (0.00 sec)

刚开始的时候考虑的是，相邻的两个调换位置

那就是奇数 -1，偶数 +1，然后再排序，用 case 搞定，再排序

但是这样的结果，是第 5 行的序号变成了 6

然后就想到了窗口函数当中的排序，有一个根据行号的

我原本的想法还是，外面再套一个查询，然后用窗口函数

但是报错，后来发现好像也可以解决，当时是没有给内查询的结果表起别名

mysql> SELECT ROW_NUMBER() OVER (ORDER BY res.Id) AS Id,->        res.Name-> FROM(->   SELECT ->     (CASE WHEN Id MOD 2 = 0 THEN Id-1 ELSE Id+1 END) AS Id,->     Name->   FROM seat->   ORDER BY Id-> ) AS res-> ;
+----+---------+
| Id | Name    |
+----+---------+
|  1 | Doris   |
|  2 | Abbot   |
|  3 | Green   |
|  4 | Emerson |
|  5 | Jeames  |
+----+---------+
5 rows in set (0.00 sec)

3. 分数排名

先建表

mysql> CREATE TABLE IF NOT EXISTS score-> ( class INTEGER PRIMARY KEY ,->   score_avg INTEGER-> );
Query OK, 0 rows affected, 1 warning (0.01 sec)mysql> 
mysql> INSERT INTO score VALUES->   (1, 93),->   (2, 93),->   (3, 93),->   (4, 91)-> ;
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0mysql> 
mysql> SELECT * FROM score;
+-------+-----------+
| class | score_avg |
+-------+-----------+
|     1 |        93 |
|     2 |        93 |
|     3 |        93 |
|     4 |        91 |
+-------+-----------+
4 rows in set (0.00 sec)

再排序

mysql> SELECT  class->        ,score_avg->        ,RANK() OVER (ORDER BY score_avg DESC) AS ranking->        ,DENSE_RANK() OVER (ORDER BY score_avg DESC) AS dense_ranking->        ,ROW_NUMBER() OVER (ORDER BY score_avg DESC) AS row_num-> FROM score; 
+-------+-----------+---------+---------------+---------+
| class | score_avg | ranking | dense_ranking | row_num |
+-------+-----------+---------+---------------+---------+
|     1 |        93 |       1 |             1 |       1 |
|     2 |        93 |       1 |             1 |       2 |
|     3 |        93 |       1 |             1 |       3 |
|     4 |        91 |       4 |             2 |       4 |
+-------+-----------+---------+---------------+---------+
4 rows in set (0.00 sec)

那就是，窗口函数的三种排序，跳序号、不跳序号、按序号

4. 连续出现的数字

老规矩，先建表

mysql> CREATE TABLE IF NOT EXISTS exercise-> ( Id INTEGER PRIMARY KEY ,->   Num INTEGER-> );
Query OK, 0 rows affected (0.05 sec)mysql> 
mysql> INSERT INTO exercise VALUES->   (1, 1),->   (2, 1),->   (3, 1),->   (4, 2),->   (5, 1),->   (6, 2),->   (7, 2)-> ;
Query OK, 7 rows affected (0.01 sec)
Records: 7  Duplicates: 0  Warnings: 0mysql> 
mysql> SELECT * FROM exercise;
+----+------+
| Id | Num  |
+----+------+
|  1 |    1 |
|  2 |    1 |
|  3 |    1 |
|  4 |    2 |
|  5 |    1 |
|  6 |    2 |
|  7 |    2 |
+----+------+
7 rows in set (0.00 sec)

再查询

mysql> SELECT ->   ori.Num AS ConsecutiveNums-> FROM (->   SELECT ->         Num,->         COUNT(*) AS cnt->   FROM exercise->   GROUP BY Num-> ) AS ori-> WHERE ori.cnt > 3-> ;
+-----------------+
| ConsecutiveNums |
+-----------------+
|               1 |
+-----------------+
1 row in set (0.00 sec)

我宣布，子查询永远滴神

先搞个内查询，按数字进行分组，然后计数出现次数

接着外查询再对结果进行筛选

5. 树节点

先建表

mysql> DROP TABLE IF EXISTS exercise;
Query OK, 0 rows affected (0.03 sec)mysql> 
mysql> CREATE TABLE IF NOT EXISTS exercise-> ( id INTEGER PRIMARY KEY ,->   p_id INTEGER-> );
Query OK, 0 rows affected (0.04 sec)mysql> 
mysql> INSERT INTO exercise VALUES->   (1, NULL),->   (2, 1),->   (3, 1),->   (4, 2),->   (5, 2)-> ;
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0mysql> 
mysql> SELECT * FROM exercise;
+----+------+
| id | p_id |
+----+------+
|  1 | NULL |
|  2 |    1 |
|  3 |    1 |
|  4 |    2 |
|  5 |    2 |
+----+------+
5 rows in set (0.00 sec)

节点分三类，根节点，叶子节点，和内节点

我们可以发现，一般来说，最广泛的应该是内节点

所以上来先把全部置为内节点

然后如果父节点为 null，那就是根节点

对于没有子节点的节点，就置为叶子节点

什么叫没有子节点呢？就是 p_id 列没有它们的 id

mysql> SELECT ->  ori.id,->  (CASE WHEN id = (SELECT id FROM exercise WHERE p_id IS NULL)->   THEN "Root"->   ELSE ori.Type->   END) AS Type-> FROM (->  SELECT->  id,->  (CASE WHEN id IN (SELECT p_id FROM exercise)->   THEN "Inner"->   ELSE "Leaf"->   END) AS Type->  FROM exercise-> ) AS ori-> ;
+----+-------+
| id | Type  |
+----+-------+
|  1 | Root  |
|  2 | Inner |
|  3 | Leaf  |
|  4 | Leaf  |
|  5 | Leaf  |
+----+-------+
5 rows in set (0.00 sec)

嘛，遇事不决子查询，不过我倒是在顺序上耽误了一会儿

如同我们上文的理论，当 id 出现在 p_id 的时候，意味着有子节点

所以说，按照这个我们能分开根节点、内节点，以及叶子节点

然后如果根据，p_id 为 null，我们能分开根节点，和内节点、叶子节点

然后 case when else，只是个二元的关系，所以我们要套一个子查询来处理三元

然后，应该先分离叶子节点，因为另外两个根据父节点就能分开

如果先分出了根节点，那么在第二次分类的时候，根节点又和内节点混合在一起了

啊，其实也可以，先分根节点，然后其他置为内节点

然后查出 id 不在 p_id 的行，更新为叶子节点就好了

6. 至少有五名直接下属的经理

建表

mysql> DROP TABLE IF EXISTS exercise;
Query OK, 0 rows affected (0.04 sec)mysql> CREATE TABLE IF NOT EXISTS exercise-> ( id INTEGER PRIMARY KEY ,->   Name VARCHAR(10),->   Department VARCHAR(5),->   ManagerId INTEGER-> );
Query OK, 0 rows affected (0.07 sec)mysql> INSERT INTO exercise VALUES->   (101, 'John', 'A', NULL),->   (102, 'Dan', 'A', 101),->   (103, 'James', 'A', 101),->   (104, 'Amy', 'A', 101),->   (105, 'Anne', 'A', 101),->   (106, 'Ron', 'B', 101)-> ;
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0mysql> SELECT * FROM exercise;
+-----+-------+------------+-----------+
| id  | Name  | Department | ManagerId |
+-----+-------+------------+-----------+
| 101 | John  | A          |      NULL |
| 102 | Dan   | A          |       101 |
| 103 | James | A          |       101 |
| 104 | Amy   | A          |       101 |
| 105 | Anne  | A          |       101 |
| 106 | Ron   | B          |       101 |
+-----+-------+------------+-----------+
6 rows in set (0.00 sec)

然后查询

mysql> SELECT ->  (SELECT Name FROM exercise WHERE id = ori.ManagerId) AS Name-> FROM (->  SELECT->  ManagerId,->  COUNT(*) AS cnt->  FROM exercise->  GROUP BY ManagerId-> ) AS ori-> WHERE ori.cnt = 5-> ;
+------+
| Name |
+------+
| John |
+------+
1 row in set (0.01 sec)

首先是筛选条件，手下有 5 名员工，意味着 id 在 ManagerId 列出现 5 次

然后拿到这个 ManagerId 后，要回过来根据 id 找 Name，套个子查询就好

我原本想在 count 次数的时候直接用 where，后来发现这是不行的

因为那是，先 where 再 group，where 没办法筛选次数

7. 查询回答率最高的问题

建表

mysql> DROP TABLE IF EXISTS exercise;
Query OK, 0 rows affected, 1 warning (0.02 sec)mysql> CREATE TABLE IF NOT EXISTS exercise-> ( index_id INTEGER PRIMARY KEY,->   uid_id INTEGER,->   action VARCHAR(10),->   question_id INTEGER,->   answer_id INTEGER,->   q_num INTEGER,->   timestamp INTEGER-> );
Query OK, 0 rows affected (0.04 sec)mysql> INSERT INTO exercise VALUES->   (1, 5, 'show', 285, NULL, 1, 123),->   (2, 5, 'answer', 285, 124, 1, 124),->   (3, 5, 'show', 369, NULL, 2, 125),->   (4, 5, 'show', 369, NULL, 2, 126)-> ;
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0mysql> SELECT * FROM exercise;
+----------+--------+--------+-------------+-----------+-------+-----------+
| index_id | uid_id | action | question_id | answer_id | q_num | timestamp |
+----------+--------+--------+-------------+-----------+-------+-----------+
|        1 |      5 | show   |         285 |      NULL |     1 |       123 |
|        2 |      5 | answer |         285 |       124 |     1 |       124 |
|        3 |      5 | show   |         369 |      NULL |     2 |       125 |
|        4 |      5 | show   |         369 |      NULL |     2 |       126 |
+----------+--------+--------+-------------+-----------+-------+-----------+
4 rows in set (0.00 sec)

查询

mysql> SELECT->  o3.question_id-> FROM exercise AS o3-> WHERE o3.action = 'answer'-> GROUP BY o3.question_id-> HAVING COUNT(o3.timestamp) = (->   SELECT->    MAX(o2.cnt)->   FROM (->    SELECT->     COUNT(o1.timestamp) AS cnt->    FROM exercise AS o1->    WHERE o1.action = 'answer'->    GROUP BY o1.question_id->   ) AS o2-> )-> ;
+-------------+
| question_id |
+-------------+
|         285 |
+-------------+
1 row in set (0.00 sec)

哇，这玩意儿可真复杂，我还以为有更好的解法

但是搜了搜，好像没有，只能嵌套

所以我们最内层的是对回答个数进行计数

然后套一个查询去拿到最大的回答次数

然后再用这个最大去对回答计数进行筛选

我感觉还有一个思路，最大嘛，那就排序

计数、count，然后降序，limit 1，取首行就是最大了

8. 各部门前3高工资的员工

建表

mysql> DROP TABLE IF EXISTS exercise;
Query OK, 0 rows affected (0.03 sec)mysql> CREATE TABLE IF NOT EXISTS exercise-> ( index_id INTEGER PRIMARY KEY,->   name VARCHAR(10),->   salary INTEGER,->   department_id INTEGER-> );
Query OK, 0 rows affected (0.04 sec)mysql> INSERT INTO exercise VALUES->   (1, 'Joe', 70000, 1),->   (2, 'Henry', 80000, 2),->   (3, 'Sam', 60000, 2),->   (4, 'Max', 90000, 1),->   (5, 'Janet', 69000, 1),->   (6, 'Randy', 85000, 1)-> ;
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0mysql> 
mysql> SELECT * FROM exercise;
+----------+-------+--------+---------------+
| index_id | name  | salary | department_id |
+----------+-------+--------+---------------+
|        1 | Joe   |  70000 |             1 |
|        2 | Henry |  80000 |             2 |
|        3 | Sam   |  60000 |             2 |
|        4 | Max   |  90000 |             1 |
|        5 | Janet |  69000 |             1 |
|        6 | Randy |  85000 |             1 |
+----------+-------+--------+---------------+
6 rows in set (0.00 sec)

查询

mysql> SELECT->  *-> FROM (->  SELECT->   *,->   ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num->  FROM exercise->  ) AS ori-> WHERE ori.row_num < 4-> ;
+----------+-------+--------+---------------+---------+
| index_id | name  | salary | department_id | row_num |
+----------+-------+--------+---------------+---------+
|        4 | Max   |  90000 |             1 |       1 |
|        6 | Randy |  85000 |             1 |       2 |
|        1 | Joe   |  70000 |             1 |       3 |
|        2 | Henry |  80000 |             2 |       1 |
|        3 | Sam   |  60000 |             2 |       2 |
+----------+-------+--------+---------------+---------+
5 rows in set (0.00 sec)

要取前三，还是分组取各组前三，那就是窗口函数，分组排序

然后再套一个查询，筛选出来排名小于 4 的即可

按照答案的格式，还需要再整理一下展示的结果

这里就不做赘述了，思路见第一题

9. 平面上最近距离

建表

mysql> DROP TABLE IF EXISTS exercise;
Query OK, 0 rows affected (0.03 sec)mysql> CREATE TABLE IF NOT EXISTS exercise-> ( index_id INTEGER PRIMARY KEY,->   x INTEGER,->   y INTEGER-> );
Query OK, 0 rows affected (0.04 sec)mysql> INSERT INTO exercise VALUES->   (1, -1, -1),->   (2, 0, 0),->   (3, -1, -2)-> ;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0mysql> SELECT * FROM exercise;
+----------+------+------+
| index_id | x    | y    |
+----------+------+------+
|        1 |   -1 |   -1 |
|        2 |    0 |    0 |
|        3 |   -1 |   -2 |
+----------+------+------+
3 rows in set (0.00 sec)

查询

mysql> SELECT->  MIN(o3.dis) AS shortest-> FROM (->  SELECT->   ROUND(->    SQRT(->     (o1.x-o2.x)*(o1.x-o2.x)->      +->     (o1.y-o2.y)*(o1.y-o2.y)->    ),2) AS dis->  FROM exercise AS o1->  CROSS JOIN exercise AS o2-> ) AS o3-> WHERE o3.dis != 0-> ;
+----------+
| shortest |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

算距离，然后保留小数，接着套一个查询去过滤 0，再求最小

10. 行程和用户

建表，麻了，不想手写了，推荐一个网站 https://tableconvert.com/

虽然起手就是 varchar(300)，但是不用动手写了

mysql> DROP TABLE IF EXISTS exercise;
Query OK, 0 rows affected (0.03 sec)mysql> CREATE TABLE IF NOT EXISTS exercise-> (->     Id varchar(300),->     Client_Id varchar(300),->     Driver_Id varchar(300),->     City_Id varchar(300),->     Status      varchar(300),->     Request_at varchar(300)-> );
Query OK, 0 rows affected (0.04 sec)mysql> INSERT INTO exercise VALUES ->  ('1', '1', '10', '1', '     completed      ', '2013-10-1 '),->  ('2', '2', '11', '1', ' cancelled_by_driver', '2013-10-1 '),->  ('3', '3', '12', '6', '     completed      ', '2013-10-1 '),->  ('4', '4', '13', '6', ' cancelled_by_client', '2013-10-1 '),->  ('5', '1', '10', '1', '     completed      ', '2013-10-2 '),->  ('6', '2', '11', '6', '     completed      ', '2013-10-2 '),->  ('7', '3', '12', '6', '     completed      ', '2013-10-2 '),->  ('8', '2', '12', '12', '     completed      ', '2013-10-3 '),->  ('9', '3', '10', '12', '     completed      ', '2013-10-3 '),->  ('10', '4', '13', '12', ' cancelled_by_driver', '2013-10-3 ')-> ;
Query OK, 10 rows affected (0.02 sec)
Records: 10  Duplicates: 0  Warnings: 0mysql> SELECT * FROM exercise;
+------+-----------+-----------+---------+----------------------+------------+
| Id   | Client_Id | Driver_Id | City_Id | Status               | Request_at |
+------+-----------+-----------+---------+----------------------+------------+
| 1    | 1         | 10        | 1       |      completed       | 2013-10-1  |
| 2    | 2         | 11        | 1       |  cancelled_by_driver | 2013-10-1  |
| 3    | 3         | 12        | 6       |      completed       | 2013-10-1  |
| 4    | 4         | 13        | 6       |  cancelled_by_client | 2013-10-1  |
| 5    | 1         | 10        | 1       |      completed       | 2013-10-2  |
| 6    | 2         | 11        | 6       |      completed       | 2013-10-2  |
| 7    | 3         | 12        | 6       |      completed       | 2013-10-2  |
| 8    | 2         | 12        | 12      |      completed       | 2013-10-3  |
| 9    | 3         | 10        | 12      |      completed       | 2013-10-3  |
| 10   | 4         | 13        | 12      |  cancelled_by_driver | 2013-10-3  |
+------+-----------+-----------+---------+----------------------+------------+
10 rows in set (0.00 sec)
mysql> DROP TABLE IF EXISTS tableName;
Query OK, 0 rows affected (0.02 sec)mysql> CREATE TABLE tableName -> (->     Users_Id    varchar(300),->     Banned      varchar(300),->     Role_name     varchar(300)-> );
Query OK, 0 rows affected (0.05 sec)mysql> INSERT INTO tableName VALUES -> ('1', 'No', 'client '),-> ('2', 'Yes', 'client '),-> ('3', 'No', 'client '),-> ('4', 'No', 'client '),-> ('10', 'No', 'driver '),-> ('11', 'No', 'driver '),-> ('12', 'No', 'driver '),-> ('13', 'No', 'driver')-> ;
Query OK, 8 rows affected (0.01 sec)
Records: 8  Duplicates: 0  Warnings: 0mysql> SELECT * FROM tableName;
+----------+--------+-----------+
| Users_Id | Banned | Role_name |
+----------+--------+-----------+
| 1        | No     | client    |
| 2        | Yes    | client    |
| 3        | No     | client    |
| 4        | No     | client    |
| 10       | No     | driver    |
| 11       | No     | driver    |
| 12       | No     | driver    |
| 13       | No     | driver    |
+----------+--------+-----------+
8 rows in set (0.00 sec)

查询

mysql> SELECT->  ori.Request_at AS Day,->  ROUND(AVG(flag),2) AS Cancellation_Rate-> FROM (->  SELECT->   *,->   (CASE WHEN Status='     completed      ' THEN 0 ELSE 1 END) AS flag->  FROM->   exercise->  WHERE->   Request_at >= '2013-10-1 '->    AND->   Request_at <= '2013-10-3 '->    AND->   Client_Id NOT IN (SELECT Users_Id FROM tableName WHERE Banned = 'Yes')-> ) AS ori-> GROUP BY Request_at-> ;
+------------+-------------------+
| Day        | Cancellation_Rate |
+------------+-------------------+
| 2013-10-1  |              0.33 |
| 2013-10-2  |              0.00 |
| 2013-10-3  |              0.50 |
+------------+-------------------+
3 rows in set (0.00 sec)

首先呢，得说说这个网站的弊端

起手 varchar(300)，然后总是在数据前后加空格

你看我选状态的时候，也得加空格，选日期的时候，也得加空格

要保证筛选时设置的条件数据，和建表时的一致

接着说说过程吧，反正得先按照条件选数据

一个是日期范围，还有一个是不在黑名单上

然后新增一个列，设撤销为 1，其他为 0，这样平均值就是比率

然后套一个查询，分组求平均，再保留小数

查看全文

http://www.jsqmd.com/news/429314/