SQL

选择

595.大的国家

如果一个国家满足下述两个条件之一，则认为该国是 大国 ： 面积至少为 300 平方公里（即，3000000 km2），或者 人口至少为 2500 万（即 25000000） 编写一个 SQL 查询以报告 大国 的国家名称、人口和面积。

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

183.从不订购的用户

某网站包含两个表，Customers 表和 Orders 表。编写一个 SQL 查询，找出所有从不订购任何东西的客户。

select `Name` as Customers from  Customers
where Id Not in(select CustomerID from Orders);

select C.Name as Customers from Customers C
left join Orders O
on C.Id = O.CustomerId
where O.CustomerId is Null;

584. 寻找用户推荐人

select name from customer 
where ifnull(referee_id,0) != 2;

select name from customer
where id  not in 
(select id from customer where  referee_id =2)

1757. 可回收且低脂的产品

select product_id from Products
where low_fats = 'Y' and recyclable = 'Y';

排序＆修改

1873. 计算特殊奖金

SELECT employee_id, 
CASE 
    WHEN employee_id%2 = 1 AND name NOT LIKE 'M%'
    THEN salary
    ELSE 0
END AS bonus
FROM Employees

select  employee_id,
if(employee_id%2 = 1 && left(name,1) != 'M',salary,0) as bonus
from   Employees;

627.变更性别

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

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

196.删除重复的电子邮件

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

字符串处理函数

1667修复表中的名字

select user_id,
concat(upper(left(name, 1)),lower(right(name, length(name) - 1))) as name
from users
order by user_id;

select user_id,
concat(upper(left(name, 1)), lower(substring(name, 2))) as name
from users
order by user_id;

1484. 按日期分组销售产品

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

1527.患某种疾病的患者

select patient_id, patient_name, conditions
from patients
where conditions like "DIAB1%" or conditions like "% DIAB1%";

组合查询＆指定选取

1965.丢失信息的雇员

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

1795.每个产品在不同商店的价格

select product_id,'store1' store, store1 price from Products where store1 is not null
union all
select product_id,'store2', store2 from Products where store2 is not null
union all
select product_id,'store3', store3 from Products where store3 is not null

608.树节点

select id,
case when t.p_id is null then 'Root' 
     when t.id in (select p_id from tree ) then 'Inner'
     else 'Leaf' 
end as Type
from tree t 

176.第二高的薪水

select max(Salary) SecondHighestSalary
from employee
where salary < (select max(salary) from employee)

select (select distinct salary from Employee order by salary desc limit 1,1) as SecondHighestSalary