Task06:秋招秘籍 A
教程地址
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: 0
mysql> 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: 0
mysql> 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: 0
mysql>
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: 0
mysql>
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: 0
mysql>
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: 0
mysql>
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: 0
mysql> 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: 0
mysql> 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: 0
mysql>
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: 0
mysql> 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: 0
mysql> 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: 0
mysql> 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,这样平均值就是比率
然后套一个查询,分组求平均,再保留小数