leetcode Database温习

183. Customers Who Never Order

https://leetcode.com/problems/customers-who-never-order/

方法一：

SELECT A.Name from Customers A
WHERE NOT EXISTS (SELECT 1 FROM Orders B WHERE A.Id = B.CustomerId)

如果存在相等返回1，如果没有返回就输出A.Name

方法二：

SELECT A.Name from Customers A
LEFT JOIN Orders B on  a.Id = B.CustomerId
WHERE b.CustomerId is NULL

联合查找，找出b 中包含A的有保存，没有置为NULL

方法三：

SELECT A.Name from Customers A
WHERE A.Id NOT IN (SELECT B.CustomerId from Orders B)

巧妙利用not in

方法四：

select Name  as Customers
from Customers
where 1>(select count(*) from Orders where Orders.CustomerId=Customers.Id)

对每一个进行count，没有出现的则输出

技巧点：exists/not exists/in/not in/count>nums 以及left jion

262. Trips and Users

https://leetcode.com/problems/trips-and-users/

利用表2对表1的约束对表1进行预处理生成临时表

预处理代码：

select * from Trips  
where client_id not in (select users_id from Users where banned = "yes" and role = "client") and request_at >= "2013-10-01" and request_at <= "2013-10-03"

也可以改为between and,代码更简洁

select * from Trips  
where client_id not in (select users_id from Users where banned = "yes" and role = "client") and request_at between "2013-10-01" and "2013-10-03"

当然也可以用in,更容易理解

select * from Trips  
where client_id not in (select users_id from Users where banned = "yes" and role = "client") and request_at in ("2013-10-01",  "2013-10-02", "2013-10-03")

生成的临时表，统计没有completed的人数以及总数，相除就是最后结果

select request_at as Day ,round(sum(case when status="completed" then 0 else 1 end)/count(*),2)as 'Cancellation Rate'
from  
(select * from Trips  
where client_id not in (select users_id from Users where banned = "yes" and role = "client") and request_at >= "2013-10-01" and request_at <= "2013-10-03") t  
group by request_at order
 by request_at asc  

技巧点：临时表，round，sum,case-when-then-else , count,

196. Delete Duplicate Emails

https://leetcode.com/problems/delete-duplicate-emails/

考察：mysql的delete操作

方法一：

delete from Person
where Id not in
(select Id  from (select min(Person.Id) as Id from Person group by Person.Email) as TomoveId)

生成一个临时表，作为not in的比较操作

方法二：

delete p1 
from Person p1,Person p2
where p1.Email=p2.Email and p1.Id>p2.Id

用两个表，另外一个表作为参考，此时delete用法类似于select

技巧点：delete类似于select

180. Consecutive Numbers

https://leetcode.com/problems/consecutive-numbers/

考察：在一个表里；连续出现一个相同的值，可能三个，四个五个

容易出现的误区：在cursor同时比较三个值，最简单的方法就是用三个table表示这种关系

select distinct(a.Num) as ConsecutiveNums
from
Logs a,Logs b,Logs c
where
b.Id=a.id+1 and c.Id=b.Id+1 and a.Num=b.Num and b.Num=c.Num

解决四次，五次六次的情况，需要建立变量

<pre name="code" class="sql">select distinct(r.Num) from
(
select Num, 
    case when @cur=Num then @count:=@count+1
    case when @cur<> @cur:=num then @count:=1 
    end 
    as n
    from Logs,(select @count:=0,@cur:=(select 1 from Logs limit 0,1)
)r
where r.n>2 

巧妙之处在于，没用命名的中间table完成了变量的初始化

补充知识点：

：=和=在MYSQL里面的区别：前者用于变量的赋值，后者用于等于的判断

运算优先级：@cur<>@cur:=num 先计算@cur的值，然后再做比较，

技巧点：多个临时表的灵活运用，以及变量的

185.Department Top Three Salaries

https://leetcode.com/problems/department-top-three-salaries/

考察：找到前面最大的三个数，但是数据可以重复

方法一：

select d.Name Department, e1.Name Employee, e1.Salary
from Employee e1 
join Department d
on e1.DepartmentId = d.Id
where 3 > (select count(distinct(e2.Salary)) 
                  from Employee e2 
                  where e2.Salary > e1.Salary 
                  and e1.DepartmentId = e2.DepartmentId
                  );

生成一个临时表，对当前salary比较统计，只有当前面比它的数为0,1,2时，当前salary分别为最大值，第二大值，第三值，不过遗留问题是 输出排序

方法二：

select d.Name Department, e1.Name Employee, e1.Salary
from Employee e1, Department d
where (select count(distinct(Salary))from Employee where Salary>e1.Salary and DepartmentId =e1.DepartmentId )in (0,1,2)
and e1.DepartmentId =d.Id
order by d.Id asc,e1.Salary desc

解决了排序的问题

SELECT
    d.Name Department, e.Name Employee, e.Salary Salary
FROM
    (
        SELECT DepartmentId, Name, Salary 
        FROM Employee 
        WHERE 3 > (
            SELECT COUNT(e1.Salary)
            FROM (
                SELECT DISTINCT Salary, DepartmentId 
                FROM Employee 
            ) e1
            WHERE
                Employee.DepartmentId = e1.DepartmentId 
            AND
                Employee.Salary < e1.Salary
        )
        ORDER BY DepartmentId ASC, Salary DESC
    ) e 
LEFT JOIN
    Department d
ON 
    e.DepartmentId = d.Id
WHERE
    d.Name IS NOT NULL

生成临时表， 然后left jion