TASK6--实战2

20190522更新第一题

20190427更新 着重第2题

20190513更新 第二题是否 distinct

第一题

不会：嘤嘤嘤

SELECT 
  Request_at AS Day, 
  FORMAT(AVG(IF(Status LIKE 'cancelled%', 1, 0)), 2) AS CancellationRate  
FROM Trips 
WHERE Client_Id NOT IN (
  SELECT Users_Id FROM Users WHERE Banned = 'YES'
  ) 
GROUP BY Request_at;

上面那个做法忘了吧

首先这是自己想的bug

SELECT t.Request_at AS 'Day' ,
         (ROUND((COUNT(CASE WHEN t.Status <> 'completed' THEN 1 ELSE NULL END)/COUNT(t.Id)),2)) AS 'Cancellation Rate'
  FROM Trips t,Users u
 WHERE (t.Client_Id = u.Users_Id OR t.Driver_Id = u.Users_Id)
   AND u.Banned = 'No'
   AND t.Request_at BETWEEN '2013-10-01' AND '2013-10-03'
 GROUP BY t.Request_at;

 首先哪里出了bug呢，因为 WHERE (t.Client_Id = u.Users_Id OR t.Driver_Id = u.Users_Id)

这里连接没有逻辑啊，其实就是认为的将原数据扩大2倍，没有达到真实目的

我们这样

这变成啥了？就是第一个表cid和did都会对应uid，相当于连接之后认为的把u1只变成只有client的id了，u2变成只有driver的id了

其实逻辑更清楚一点是这样

再进一步

 

最后

 

 

 

第2题（自己不太会）

见TASK5的另一个连接表 

-- 创建表
DROP TABLE employee;
CREATE TABLE employee
(
Id            INT          NOT NULL ,
Name          VARCHAR(50)  NOT NULL,
Salary        INT          NOT NULL,
DepartmentId  INT          NOT NULL
);

INSERT INTO employee(Id,Name,Salary,DepartmentId) VALUES (1,'Joe',70000,1);
INSERT INTO employee(Id,Name,Salary,DepartmentId) VALUES (2,'Henry',80000,2);
INSERT INTO employee(Id,Name,Salary,DepartmentId) VALUES (3,'Sam',60000,2);
INSERT INTO employee(Id,Name,Salary,DepartmentId) VALUES (4,'Max',90000,1);
INSERT INTO employee(Id,Name,Salary,DepartmentId) VALUES (5,'Janet',69000,1);
INSERT INTO employee(Id,Name,Salary,DepartmentId) VALUES (6,'Randy',85000,1);

Drop TABLE Department;
CREATE TABLE Department ( 
Id            INT          NOT NULL PRIMARY KEY, 
Name          VARCHAR(50)  NOT NULL 
);

INSERT INTO Department(Id,Name) VALUES (1,'IT');
INSERT INTO Department(Id,Name) VALUES (2,'Sales');

-- 找出每个部门工资高的学生

SET @limit_n = 3;  -- 定义变量，筛选各部门前N个工资最高的人
SELECT
  d. NAME Department,
  e. NAME Employee,
  e.Salary
FROM
  Employee e
INNER JOIN Department d ON e.DepartmentId = d.ID
WHERE
  (
    SELECT
      COUNT(1)
    FROM
      Employee e2
    WHERE
      e2.DepartmentID = d.ID
    AND e2.Salary > e.Salary
  ) < @limit_n
ORDER BY d.Name,e.Salary DESC;

-- 上题思路：对于每一个e.Salary,有多少个大于该Salary的，因为找前三个，所以小于3个就好（1,2）=3代表第四名了 
-- 这里where e2.DepartmentID = d.ID表明对于每一个e.Salary，用e2.Salary去比较的时候，保证类别一样 

现在我有INSERT一条lych

方法1：

SELECT d.Name AS Department,
        e.Name AS Employee,
        e.Salary AS Salary
  FROM Department as d
  JOIN Employee AS e
    ON e.DepartmentId = d.Id
 WHERE (SELECT COUNT(1)
          FROM Employee e2
         WHERE e2.DepartmentId = d.Id
           AND e2.Salary > e.salary) < 3
ORDER BY Department,Salary DESC;

返回：

 

方法二： 

SELECT d.Name AS Department,
        e.Name AS Employee,
        e.Salary AS Salary
  FROM Department as d
  JOIN Employee AS e
    ON e.DepartmentId = d.Id
 WHERE (SELECT COUNT(DISTINCT e2.salary)
          FROM Employee e2
         WHERE e2.DepartmentId = d.Id
           AND e2.Salary > e.salary) < 3
ORDER BY Department,Salary DESC

返回 

 

 

看到加DISTINCT e2.Salary的差别了吗：加的话，会显示前三名会把排名一样的人当做一个人处理，不加只会显示前3个名，建议加上

 

第三题：

 

这个题做出来了，但是做的很困难

DROP   TABLE scores;
CREATE TABLE scores ( 
Id            INT          NOT NULL , 
Score         DECIMAL(10,2) NOT NULL 
);

INSERT INTO scores(Id,Score) VALUES (1,3.50);
INSERT INTO scores(Id,Score) VALUES (2,3.65);
INSERT INTO scores(Id,Score) VALUES (3,4.00);
INSERT INTO scores(Id,Score) VALUES (4,3.85);
INSERT INTO scores(Id,Score) VALUES (5,4.00);
INSERT INTO scores(Id,Score) VALUES (6,3.65);

SELECT * FROM scores;

SELECT s1.score  ,
 (SELECT COUNT(s2.score)+1   FROM scores s2
	    WHERE s2.score > s1.score ) as '排名'
	FROM scores s1
    ORDER BY score DESC;

思路如下：

注 紧排名和松排名之间只差了一个distinct

SELECT s1.*,
      (SELECT COUNT(DISTINCT Score) + 1
         FROM scores s2
		WHERE s2.score > s1.score) AS 'rank1',
        (SELECT COUNT( Score) + 1
         FROM scores s2
		WHERE s2.score > s1.score) AS 'rank2'
  FROM scores s1
  ORDER BY s1.score DESC;