Leecode SQL语句

196. 删除重复的电子邮箱

表: Person

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| email       | varchar |
+-------------+---------+

编写一个 SQL 删除语句来 删除 所有重复的电子邮件，只保留一个id最小的唯一电子邮件。

答案

delete p1 from Person as p1, Person as p2 where p1.email = p2.email and p1.id > p2.id;

627. 变更性别

Salary 表：

+-------------+----------+
| Column Name | Type     |
+-------------+----------+
| id          | int      |
| name        | varchar  |
| sex         | ENUM     |
| salary      | int      |
+-------------+----------+
id 是这个表的主键。
sex 这一列的值是 ENUM 类型，只能从 ('m', 'f') 中取。
本表包含公司雇员的信息。

思路

三目运算符

答案

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

1873. 计算特殊奖金

表: Employees

+-------------+---------+
| 列名        | 类型     |
+-------------+---------+
| employee_id | int     |
| name        | varchar |
| salary      | int     |
+-------------+---------+

答案

# 方法一： 三目运算符
SELECT employee_id,
IF(MOD(employee_id,2)!=0 AND LEFT(name,1)!='M',salary,0) bonus
FROM Employees ORDER BY employee_id

# 方法二： case when then
select employee_id, (case when employee_id % 2 = 1 and name not like "M%"then salary 
else 0 end) as bonus from Employees 

1667. 修复表中的名字

表： Users

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| user_id        | int     |
| name           | varchar |
+----------------+---------+
user_id 是该表的主键。
该表包含用户的 ID 和名字。名字仅由小写和大写字符组成。	 

编写一个 SQL 查询来修复名字，使得只有第一个字符是大写的，其余都是小写的。

答案：

● CONCAT 用来拼接字符串 
● LEFT 从左边截取字符 
● RIGHT 从右边截取字符
● UPPER 变为大写
● LOWER 变为小写
● LENGTH 获取字符串长度

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

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

表：Products

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| product_id  | int     |
| store1      | int     |
| store2      | int     |
| store3      | int     |
+-------------+---------+
这张表的主键是product_id（产品Id）。
每行存储了这一产品在不同商店store1, store2, store3的价格。
如果这一产品在商店里没有出售，则值将为null。

解答：

# 行转列用groupby+sumif，列转行用union all
select product_id, "store1" store, store1 price from Products where store1 is not null 
union all 
select product_id, "store2" store, store2 price from Products where store2 is not null 
union all 
select product_id, "store3" store, store3 price from Products where store3 is not null;

608. 树节点

给定一个表 tree，id 是树节点的编号， p_id 是它父节点的 id 。

+----+------+
| id | p_id |
+----+------+
| 1  | null |
| 2  | 1    |
| 3  | 1    |
| 4  | 2    |
| 5  | 2    |
+----+------+
树中每个节点属于以下三种类型之一：

叶子：如果这个节点没有任何孩子节点。
根：如果这个节点是整棵树的根，即没有父节点。
内部节点：如果这个节点既不是叶子节点也不是根节点。

解答

# 1、not in与null的用法
# 2、case when  xxx  then yyy
#		when  zzz  then aaa
#		else vvv

select id,
    case when p_id is null then "Root"
         when id in (select p_id from tree) then "Inner"
        else "Leaf"
    end as type
from tree;

第二高薪水

# 要想获取第二高，需要排序，使用 order by（默认是升序 asc，即从小到大），若想降序则使用关键字 desc
# 去重，如果有多个相同的数据，使用关键字 distinct 去重
# 判断临界输出，如果不存在第二高的薪水，查询应返回 null，使用 ifNull（查询，null）方法
# 起别名，使用关键字 as ...
# 因为去了重，又按顺序排序，使用 limit（）方法，查询第二大的数据，即第二高的薪水，即 limit(1,1) （因为默认从0开始，所以第一个1是查询第二大的数，第二个1是表示往后显示多少条数据，这里只需要一条）
 select ifNull((select distinct salary from Employee order by salary desc limit 1,1), null) as SecondHighestSalary ;

参考资料

mysql concat_group的用法