leecode刷题记录

类别:简单

菜逼的leecode刷题记录开始

1.去重

select distinct a.Email from Person a, Person b 
where a.Email = b.Email and a.Id != b.Id;

select email from person GROUP BY email HAVING count(email) > 1;

2.看起来简单的索引机制

select name,population,area from world 
where population > 25000000 or area > 3000000;

select name,population,area from World 
where area > 3000000 
union select name,population,area from World 
where population > 25000000;

对于索引列来最好使用union all,因复杂的查询【包含运算等】将使or、in放弃索引而全表扫描,除非你能确定or、in会使用索引。 对于只有非索引字段来说你就老老实实的用or 或者in,因为 非索引字段本来要全表扫描而union all 只成倍增加表扫描的次数

3.一个判断奇偶你至于搞出这么多方法嘛?(lll¬ω¬)

select * from cinema where id & 1 and description != "boring" order by rating desc;

select * from cinema where id % 2 = 1 and description <> 'boring' 
order by rating desc;

select * from cinema where mod(id, 2) = 1 and description != 'boring' 
order by rating desc;

4.同时做交换

update salary set sex = if(sex = 'm','f','m');

update salary set sex = (case when sex = 'm' then 'f' when sex = 'f' then 'm' end);

IF(expr1,expr2,expr3)
如果 expr1 是TRUE (expr1 <> 0 and expr1 <> NULL),则 IF()的返回值为expr2; 否则返回值则为 expr3。IF() 的返回值为数字值或字符串值,具体情况视其所在语境而定。

CASE WHEN的两种写法:
Type 1: CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result …] [ELSE result] END
Type 2: CASE WHEN [condition] THEN result [WHEN [condition] THEN result …] [ELSE result] END

5.join

select t1.firstname,t1.lastname,t2.city,t2.state from person t1 
left join address t2 on t1.personid = t2.personid; 

数据库在通过连接两张或多张表来返回记录时,都会生成一张中间的临时表,然后再将这张临时表返回给用户。 在使用left jion时,on和where条件的区别如下:

  1. on条件是在生成临时表时使用的条件,它不管on中的条件是否为真,都会返回左边表中的记录。
  2. where条件是在临时表生成好后,再对临时表进行过滤的条件。这时已经没有left join的含义(必须返回左边表的记录)了,条件不为真的就全部过滤掉。

6.笛卡尔积

select e1.Name as Employee from Employee as e1 ,Employee as e2 
where e1.ManagerId=e2.Id AND e1.Salary>e2.Salary;

select name as employee from employee as a 
where salary > (select salary from employee where id = a.managerid);

select e.Name as Employee from Employee as e 
inner join Employee as f on e.ManagerId=f.Id where e.salary>f.salary;

7.offset关键字,这个真的不知道

select distinct salary as SecondHighestSalary from employee 
order by salary desc limit 1 offset 1;

8.not in关键字

select name Customers from Customers where Id not in (select CustomerId from Orders);

9.这题卡住了,哎

SELECT
    Department.name AS 'Department',
    Employee.name AS 'Employee',
    Salary
FROM
    Employee
        JOIN
    Department ON Employee.DepartmentId = Department.Id
WHERE
    (Employee.DepartmentId , Salary) IN
    (   SELECT
            DepartmentId, MAX(Salary)
        FROM
            Employee
        GROUP BY DepartmentId
    )
;

10.group by having的用法

如果要用到group by 一般用到的就是“每”这个字 例如说明现在有一个这样的表:每个部门有多少人 就要用到分组的技术

having是分组(group by)后的筛选条件,分组后的数据组内再筛选,而where则是在分组前筛选

where子句中不能使用聚集函数,而having子句中可以,所以在集合函数中加上了HAVING来起到测试查询结果是否符合条件的作用
即having子句的适用场景是可以使用聚合函数

having 子句限制的是组,而不是行
having 子句中的每一个元素也必须出现在select列表中。有些数据库例外,如oracle
当同时含有 where 子句、group by 子句 、having 子句及聚集函数时,执行顺序如下:
执行where子句查找符合条件的数据;使用group by 子句对数据进行分组;对group by 子句形成的组运行聚集函数计算每一组的值;最后用having 子句去掉不符合条件的组

11.删除重复记录

SELECT p1.*
FROM Person p1,
    Person p2
WHERE
    p1.Email = p2.Email
;

SELECT p1.*
FROM Person p1,
    Person p2
WHERE
    p1.Email = p2.Email AND p1.Id > p2.Id
;

DELETE p1 FROM Person p1,
    Person p2
WHERE
    p1.Email = p2.Email AND p1.Id > p2.Id

12.datediff函数(日期相差天数)

select w1.id from weather w1,weather w2 
where w1.temperature > w2.temperature and DATEDIFF(w1.recorddate,w2.recorddate) = 1