大的国家

方法一：

1
2
3

SELECT name,population,area
FROM World 
WHERE area >= 3000000 or population >= 25000000

方法二：

通过union合并两个查找结果

SELECT
    name, population, area
FROM
    world
WHERE
    area >= 3000000
    
UNION

SELECT
    name, population, area
FROM
    world
WHERE
    population >= 25000000
;

可回收且低脂的产品

SELECT p.product_id
FROM Products as p
WHERE 
    p.low_fats = 'Y' 
AND 
    p.recyclable = 'Y'

寻找用户推荐人

方法一：

SELECT c.name
FROM customer as c
WHERE referee_id != 2
OR referee_id is null

注意：MySQL 使用三值逻辑 —— TRUE, FALSE 和 UNKNOWN。任何与 NULL 值进行的比较都会与第三种值 UNKNOWN 做比较。这个“任何值”包括 NULL 本身！这就是为什么 MySQL 提供 IS NULL 和 IS NOT NULL两种操作来对 NULL 特殊判断。因此，在 WHERE 语句中我们需要做一个额外的条件判断 referee_id IS NULL

方法二：

1
2
3

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

从不订购的客户

方法一：

SELECT Customers.Name AS Customers
FROM Customers
WHERE Id not IN (
    SELECT CustomerId
    FROM Orders
)

方法二：

SELECT c.Name AS Customers
FROM Customers as c
LEFT JOIN Orders AS o ON c.ID = o.CustomerId
WHERE o.ID IS NULL

计算特殊奖金

方法一：

IF(expr1,expr2,expr3)：如果expr1为TRUE (expr1 <> 0 and expr1 <> NULL)，则 IF()的返回值为expr2。否则返回值则为 expr3。IF() 的返回值为数字值或字符串值

SELECT 
    employee_id,IF(MOD(employee_id,2)!=0 AND LEFT(name,1)!='M',salary,0) bonus
FROM Employees
ORDER BY employee_id ASC

方法二：

SELECT employee_id,
CASE
    WHEN MOD(employee_id,2) = 1 AND name NOT LIKE "M%"
    THEN salary
    ELSE 0
END CASE bonus

FROM Employees
ORDER BY employee_id

变更性别

方法一：

1 2	UPDATE Salary SET sex = if(sex='m','f','m')

方法二：

UPDATE Salary
SET sex = (
    CASE sex WHEN 'm' THEN 'f' ELSE 'm' END
);

方法三：

字符和ASCII码互转

ascii():将字符转为ASCII码值

char():将ASCII码值转为字符

1 2	update salary set sex = char(ascii('m') + ascii('f') - ascii(sex));

删除重复的电子邮箱

方法一：

1
2
3

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

方法二：

DELETE FROM Person
WHERE id NOT in(
    SELECT *
    FROM (
        SELECT Min(id) 
        FROM Person
        GROUP BY email
    ) temp
)

查找重复的电子邮箱

方法一：

SELECT Distinct p1.Email 
FROM Person AS p1, Person AS p2
WHERE p1.Email = p2.Email
AND p1.Id != p2.Id

方法二：

待补充……

执行顺序

on>where>group by>having>count>order by

修复表中的名字

SELECT user_id ,
concat(upper(left(name,1)),lower(substr(name,2))) AS name
FROM Users
ORDER BY user_id

CONCAT() 函数：可以将多个字符串拼接在一起。

LEFT(str, length) 函数：从左开始截取字符串，length 是截取的长度。

UPPER(str) 与 LOWER(str)：将字符串中所有字符转为大写

LOWER(str) ：将字符串中所有字符转为小写

SUBSTRING(str, begin, end)：截取字符串，end 不写默认为空。

SUBSTRING(name, 2) ：从第二个截取到末尾，注意并不是下标，就是第二个。

按日期分组销售产品

SELECT sell_date,
    COUNT(distinct product) AS num_sold,
    #不同的(distinct product)按照字典排序(order by product)然后按照“,”分割(separator ',')
    GROUP_CONCAT(distinct product ORDER BY product separator ',') AS products
FROM Activities
GROUP BY sell_date
ORDER BY sell_date;

MYSQL GROUP_CONCAT函数 - 简书 (jianshu.com)

患某种疾病的患者

1
2
3

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

丢失信息的雇员

方法一：

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

方法二：

select employee_id
from (
    select employee_id from Employees
    union all
    select employee_id from Salaries
) temp
group by 1
having count(1)=1
order by employee_id

创建临时表

with temp as(
    select employee_id from Employees
    union all
    select employee_id from Salaries
)
select temp.employee_id
from temp
group by 1
having count(1)=1
order by employee_id

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

# Write your MySQL query statement below
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

树节点

方法一：

# 根节点：无父节点
select id,'Root' as type
from tree
where p_id is null

union all

# 内部节点：有父节点，有子节点
select id,'Inner' as type
from tree
where id in(
    select distinct p_id
    from tree 
    where p_id is not null
)
and p_id is not null

union all

# 叶子节点：有父节点，无子节点
select id,'Leaf' as type
from tree
where id not in(
    select distinct p_id
    from tree 
    where p_id is not null
)
and p_id is not null

order by id

方法二：

select 
    id,
    case
        when tree.id = (
            select atree.id
            from tree atree
            where atree.p_id is null
        ) then 'Root'
        when tree.id in(
            select atree.p_id
            from tree atree
        ) then 'Inner'
        else 'Leaf'
    end as type
from tree
order by id

第二高的薪水

方法一：

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

方法二：

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

ifNULL(A,B)：若A非空，则取A，否则取B

组合两个表

方法一：左连接

select person.FirstName,person.LastName,Address.City,Address.State
from
Person left join Address
on
Person.personid=Address.personid

总结

inner join：2表值都存在

outer join：附表中值可能存在null的情况。

A inner join B：取交集

A left join B：取A全部，B没有对应的值，则为null

A right join B：取B全部，A没有对应的值，则为null

A full outer join B：取并集，彼此没有对应的值为null

进店却未进行过交易的顾客

方法一：

select customer_id,count(visit_id) as count_no_trans
from Visits
where visit_id not in(
    select visit_id
    from Transactions
)
group by customer_id

方法二：

select v.customer_id,count(v.customer_id) - count(t.visit_id) as count_no_trans
from Visits as v
left join Transactions as t
on v.visit_id = t.visit_id
group by v.customer_id
having count_no_trans != 0

文章浏览 I

方法一：

select distinct author_id as id
from (
    select * 
    from Views
    where author_id = viewer_id
) as tmp
order by id

方法二：

select DISTINCT author_id as id
from Views
where author_id = viewer_id 
order by id

上升的温度

# 自联结
select w1.id
from Weather as w1,Weather as w2
where dateDiff(w1.recordDate,w2.recordDate) = 1
and w1.Temperature > w2.Temperature

销售员

# 子查询
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'
    )
)

第N高的薪水

方法一：ifNULL函数

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
set N = N - 1;
  RETURN (
      # Write your MySQL query statement below.
      select ifNULL((
        select distinct Salary
        from Employee
        order by Salary desc
        limit N,1),NULL) as getNthHighestSalary
  );
END

方法二：单表查询

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
set N := N - 1;
  RETURN (
      # Write your MySQL query statement below.
      select salary
      from Employee
      group by salary
      order by salary desc 
      limit N,1
  );
END

方法三：自定义变量（待补充）

分数排名

select s1.score,count(distinct(s2.score)) as 'rank'
from Scores as s1, Scores as s2
where s1.score <= s2.score
group by s1.id
order by s1.score desc

超过经理收入的员工

select e1.name as 'Employee'
from Employee as e1, Employee as e2
where e1.managerId = e2.id
and e1.salary > e2.salary

游戏玩法分析 I

1
2
3

select player_id, min(event_date) as first_login
from Activity
group by player_id

查询近30天活跃用户数

注意：dateDiff计算出来的结果可能是负数，所以需要再添加一个条件，保证activity_date小于2019-07-27

select activity_date as 'day', count(distinct user_id) as 'active_users'
from activity
where dateDiff('2019-07-27',activity_date) < 30
and activity_date < '2019-07-27'
group by activity_date

每天的领导和合伙人

select 
    date_id, 
    make_name, 
    count(distinct lead_id) as unique_leads, 
    count(distinct partner_id) as unique_partners
from DailySales 
group by date_id, make_name

求关注者的数量

select distinct user_id, count(user_id) as followers_count
from followers
group by user_id
order by user_id

部门工资最高的员工

select 
    d.name as Department, e.name as Employee, e.salary as Salary
from 
    Employee e
left join 
    Department d
on 
    e.departmentId = d.id
where(
    e.DepartmentId, Salary
)in(
    select
        DepartmentId, MAX(Salary)
    from
        Employee
    group by DepartmentId
)