LeetCode---SQL刷题2

1.1873. 计算特殊奖金

SQL语句如下:

解题1采用CASE WHEN … END:

SELECT employee_id,
CASE 
WHEN  MOD(employee_id,2) = 1 AND LEFT(name,1) <> 'M' THEN   salary
ELSE  0
END
AS bonus 
FROM Employees 
ORDER BY employee_id     
1
2
3
4
5
6
7
8

解题2【用时时间少】采用IF函数:

SELECT employee_id,
IF(MOD(employee_id,2) = 1 AND LEFT(name,1) <> 'M',salary,0) 
AS bonus 
FROM Employees 
ORDER BY employee_id 
1
2
3
4
5

2.627. 变更性别

SQl语句如下:

解题1:

UPDATE Salary SET
sex  = 
CASE WHEN sex = 'm' THEN 'f'  WHEN sex = 'f' THEN 'm'   ELSE sex END
1
2
3

解题2:

update salary set sex = char(211 - ascii(sex));
1

解题3:

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

解题4:【用时时间少】:

UPDATE Salary SET
sex  = 
CASE sex WHEN 'm' THEN 'f' WHEN 'f' THEN 'm' ELSE sex END 
1
2
3

3.196. 删除重复的电子邮箱

SQL语句如下:
解题1:

DELETE p1 
FROM Person p1,Person p2
WHERE p1.id > p2.id AND p1.email = p2.email;
1
2
3

解题2【用时时间少】:

DELETE FROM Person 
WHERE id NOT IN 
(
   SELECT * FROM  (SELECT min(id) FROM Person GROUP BY email) 
t);
1
2
3
4
5

备注:
下面这种操作是不可以的,因为不能在同一个sql语句中，先select同一个表的某些值，然后再update这个表,所以使用SELECT * FROM t ,这里的t就是重复记录中id最小的记录作为一张表进行返回。

DELETE FROM Person 
WHERE id NOT IN (SELECT min(id) FROM Person GROUP BY email);
1
2

4.1667. 修复表中的名字

SQL语句如下:
解题1:

SELECT user_id,
CONCAT(
    UPPER(LEFT(name,1)),
    LOWER(RIGHT(name,LENGTH(name) - 1))
) AS name
FROM Users
ORDER BY user_id;
1
2
3
4
5
6
7

解题2(用时较少):

SELECT user_id,
CONCAT(
    UPPER(LEFT(name,1)),
    LOWER(SUBSTRING(name,2))
) AS name
FROM Users
ORDER BY user_id;
1
2
3
4
5
6
7

5.1484. 按日期分组销售产品

SQL如下:

关于GROUP_CONCAT函数表示返回一个包含组中连接的非 NULL 值的字符串结果。来源于官方解释:GROUP_CONCAT

解题:

SELECT sell_date,
COUNT(DISTINCT product) AS num_sold ,
GROUP_CONCAT(DISTINCT product) AS products
FROM Activities 
GROUP BY sell_date
1
2
3
4
5

备注:由于GROUP BY内部默认有排序所有可以不用排序

6.1527. 患某种疾病的患者

SQL如下:
解题1:

SELECT patient_id, patient_name, conditions   
FROM Patients
WHERE conditions like 'DIAB1%'  OR conditions like '% DIAB1%'
1
2
3

解题2:

SELECT patient_id, patient_name, conditions   
FROM Patients
WHERE conditions REGEXP '\\bDIAB1'
1
2
3