leetcode-sql-刷题汇总(补充ing)

    # 思路1: not in 
    select name 
    from 
    SalesPerson
    where name not in 
    (select s.name
    from 
    Company as c
    join 
    Orders as o 
    on c.com_id = o.com_id
    join 
    SalesPerson as s 
    on o.sales_id = s.sales_id
    where c.name = 'RED')
    
    
    # 思路2: 不需要找到人名，只需要找到id，排除id即可
    select
    name
    from
    salesperson
    where
    sales_id not in 
    (select sales_id from orders where com_id in 
        (select com_id from company where name = 'RED'))

    # Write your MySQL query statement below
    
    # 这个不对，因为数据可能不是日期连续的。。。
    select id 
    from 
    (select 
    id, 
    lag(Temperature, 1) over (order by recordDate) as preTemperature, Temperature,
    lag(recordDate, 1) over (order by recordDate) as preRecordDate, recordDate
    from
    Weather)as t 
    where 1 
    and DATE_ADD(preRecordDate, interval 1 day) = recordDate
    and Temperature > preTemperature 
    
    
    
    
    # 思路2:就是笛卡尔积，然后判断出来即可
    select t2.id 
    from 
    Weather as t1 
    join 
    Weather as t2 
    where 1
    and DATE_ADD(t1.recordDate, interval 1 day) = t2.recordDate
    and t2.Temperature > t1.Temperature

    # 思路1: 就where即可
    select distinct author_id as id
    from Views 
    where author_id = viewer_id
    order by author_id

    # 思路1: left join 
    select v.customer_id, 
    count(1) as count_no_trans
    from 
    Visits as v 
    left join 
    Transactions as t 
    on v.visit_id = t.visit_id
    where t.transaction_id is null
    group by v.customer_id

    # 思路1: 就关联出父节点和子节点即可
    select id, 
    if(p_count > 0 && c_count > 0, 'Inner', if(p_count = 0, 'Root', 'Leaf')) as type
    from 
    (select t1.id, 
    # t2.id as p_id, t3.id as child_id,
    count(t2.id) as p_count,
    count(t3.id) as c_count
    from 
    tree as t1 
    left join 
    tree as t2 
    on t1.p_id = t2.id
    left join 
    tree as t3 
    on t3.p_id = t1.id
    group by id) as tt

    # Write your MySQL query statement below
    
    # 思路1: 常规列转行
    #用union all是因为它不去重，这里去不去重都不影响结果，所以选个更少运算的
    select product_id, 'store1' as store, store1 as price
    from products
    where store1 is not null
    union all 
    select product_id, 'store2' as store, store2 as price
    from products
    where store2 is not null
    union all
    select product_id, 'store3' as store, store3 as price
    from products
    where store3 is not null;
    
    
    # 思路2: mysql 不太好用
    # select product_id,lower(store) as store,price
    # from Products
    # unpivot (price for store in (store1, store2, store3))

    # 思路1: 常规思路
    select employee_id from employees
    where employee_id not in (select employee_id from salaries)
    union
    select employee_id from salaries
    where employee_id not in (select employee_id from employees)
    order by employee_id
    
    # 思路1: 常规思路
    SELECT A.employee_id
    FROM employees A LEFT JOIN salaries B ON A.employee_id = B.employee_id
    WHERE B.salary IS NULL
    UNION
    SELECT A.employee_id
    FROM salaries A LEFT JOIN employees B ON A.employee_id = B.employee_id
    WHERE B.name IS NULL
    order by employee_id
    
    # 思路2
    with tmp as(
    select employee_id from employees 
    union all
    select employee_id from salaries
    )
    select
    employee_id
    from tmp t group by employee_id having count(1)=1 order by employee_id

    # 思路1:这也可以没啥问题
    select * from Patients 
    where conditions like 'DIAB1%' or conditions like '% DIAB1%'
    
    # 思路2:正则匹配
    select 
    patient_id,patient_name,conditions
    from
    Patients
    where conditions REGEXP '^DIAB1| +DIAB1'

    select 
    sell_date, 
    count(distinct product) as num_sold,
    group_concat(distinct product order by product) as products
    from 
    Activities
    group by sell_date

    select user_id, 
    concat(upper(SUBSTRING(name,1,1)), lower(SUBSTRING(name,2,length(name)))) as name
    from
    Users
    order by user_id

    # 思路1：还是dense_rank的使用
    select Department, Employee, Salary
    from 
    (select d.name as Department, e.name as Employee,  e.salary as Salary,
    dense_rank() over (partition by d.id order by salary desc) as dense_rk
    from 
    Employee as e 
    join 
    Department as d 
    on e.departmentId = d.id) as t
    where dense_rk <= 3;

    # 思路1: dense_rank()
    select Department, name as Employee, salary
    from 
    (select d.name as Department, 
    e.name, e.salary,
    dense_rank() over (partition by d.name order by salary desc) as rn
    from 
    Employee as e 
    join 
    Department as d 
    on e.departmentId = d.id) as t 
    where t.rn = 1;
    
    # 思路2: 原来可以这样的啊
    select 
    d.Name as Department,
    e.Name as Employee,
    e.Salary 
    from 
    Employee e, Department d 
    where
    e.DepartmentId=d.id 
    and
    (e.Salary,e.DepartmentId) in 
    (select max(Salary),DepartmentId from Employee group by DepartmentId);
    
    
    # 求出最大工资，然后关联
    select t3.name department, t2.name Employee, salary
    from (
    select departmentid, max(salary) max_salary
    from Employee
    group by departmentid
    ) t1
    join Employee t2
    on t1.departmentid = t2.departmentid and t1.max_salary = t2.salary
    join department t3
    on t1.departmentid = t3.id

    # 思路1
    select Email
    from Person
    group by Email
    having count(1) > 1

    # 这里其实直接join也可以
    select emp.name as Employee
    from 
    Employee as emp
    left join 
    Employee as man
    on emp.managerId = man.id 
    where emp.salary > man.salary

    # 思路1: 直接找到最小id，删除掉not in的数据即可
    delete from Person where id not in
    (select id from 
    (select min(id) as id
    from Person
    group by email) as t)

    # 思路1: 使用ascii码
    # update Salary set sex = char(211-ascii(sex));
    # update salary set sex = char(ascii('m') + ascii('f') - ascii(sex));
    
    # 思路2: if判断
    # update salary set sex=if(sex="m","f","m");
    
    # 思路3: case when
    update salary set sex = (case sex when 'm' then 'f' else 'm' end);

    select employee_id, 
    if(employee_id%2!=0 && name not like 'M%', salary, 0) as bonus
    from Employees
    order by employee_id

    # 思路1: 分组，把连续出现的相同数组分成一组，然后统计这组的个数，大于3则符合要求筛选出来
    select distinct num as ConsecutiveNums
    from
    (select num, group_id, 
    count(1) as show_times
    from
    (select id, num, lag_num,
    sum(if(lag_num = null or lag_num = num, 0, 1)) over (order by id) as group_id
    from (
    select id, 
    num,
    lag(num, 1) over (order by id) as lag_num
    from `Logs`
    ) as t1 ) as t2
    group by num, group_id) as t3
    where show_times >= 3

    select distinct num as ConsecutiveNums 
    from(
    select id, num,
      lead(Num) over (order by Id) as nxt_num,
    lag(Num) over(order by Id) as lst_num
    FROM Logs)t
    where nxt_num=num and lst_num=num

    # 思路1:
    # 难道是没有这个函数吗
    # 艹，rank是关键字。。。
    select score, 
    dense_rank() over (order by score desc) as `rank`
    from Scores;
    
    # select
    #     score,
    #     (dense_rank() over (order by Score desc)) AS "rank" 
    # from
    #     Scores

    CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
    BEGIN
      RETURN (
        # 思路1
        select (select salary 
        from 
        (select salary, row_number() over (order by salary desc) as rn
        from (
        select DISTINCT salary from Employee) as r
        ) as t 
        where rn = N ) as SecondHighestSalary
      );
    END

    CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
    BEGIN
    SET N := N-1;
      RETURN (
        # 思路2
        # 注意：SET N := N-1;
        select (select DISTINCT salary
        from Employee
        order by salary DESC
        limit 1 offset N) as SecondHighestSalary
      );
    END

    CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
    BEGIN
    SET N := N-1;
      RETURN (
        # 思路3
        # 注意：SET N := N-1;
        select IFNULL((select DISTINCT salary
        from Employee
        order by salary DESC
        limit 1 offset N),NULL) as SecondHighestSalary
      );
    END

    CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
    BEGIN
    SET N := N-1;
      RETURN (
        # 思路4:
        # 注意：SET N := N-1;
        SELECT 
                salary
        FROM 
                employee
        GROUP BY 
                salary
        ORDER BY 
                salary DESC
        LIMIT N, 1
      );
    END

    select (select salary 
    from 
    (select salary, row_number() over (order by salary desc) as rn
    from (
    select DISTINCT salary from Employee) as r
    ) as t 
    where rn = 2 ) as SecondHighestSalary

    select (select DISTINCT salary
    from Employee
    order by salary DESC
    limit 1 offset 1) as SecondHighestSalary;

    select IFNULL((select DISTINCT salary
    from Employee
    order by salary DESC
    limit 1 offset 1),NULL) as SecondHighestSalary;

    select firstName, lastName, 
    city, state    
    from 
    Person as p 
    left join 
    Address as a 
    on p.PersonId = a.PersonId