Day02-LeetCode-SQL启航~

我的SQL学习资源为https://www.w3schools.com/sql
新手卡关基本都会找到说明的好去处,
建议快速阅览一次再刷题才会有一些查询方向。

http://img2.58codes.com/2024/20154851ZiYHW0ZDQW.jpg

1873. Calculate Special Bonus

+-------------+---------+| Column Name | Type    |+-------------+---------+| employee_id | int     || name        | varchar || salary      | int     |+-------------+---------+employee_id is the primary key for this table.Each row of this table indicates the employee ID, employee name, and salary.Write an SQL query to calculate the bonus of each employee. The bonus of an employee is 100% of their salary if the ID of the employee is an odd number and the employee name does not start with the character 'M'. The bonus of an employee is 0 otherwise.Return the result table ordered by employee_id.The query result format is in the following example.Example 1:Input: Employees table:+-------------+---------+--------+| employee_id | name    | salary |+-------------+---------+--------+| 2           | Meir    | 3000   || 3           | Michael | 3800   || 7           | Addilyn | 7400   || 8           | Juan    | 6100   || 9           | Kannon  | 7700   |+-------------+---------+--------+Output: +-------------+-------+| employee_id | bonus |+-------------+-------+| 2           | 0     || 3           | 0     || 7           | 7400  || 8           | 0     || 9           | 7700  |+-------------+-------+Explanation: The employees with IDs 2 and 8 get 0 bonus because they have an even employee_id.The employee with ID 3 gets 0 bonus because their name starts with 'M'.The rest of the employees get a 100% bonus.

这一题要我们将ID为奇数且名字不为'M'的员工bonus维持100%,其他则归0
,一开始想说用WHERE employee_id % 2 != 0 AND name NOT LIKE 'M%'去判断,
但不清楚归0的If怎么写,于是这一题拜託了GOOGLE大神,
这边要注意LeetCode Submit时会做测试,一定要写ORDER BY才不会又遇到Run ok 却Submit失败的情况。

SELECT employee_id,       (if(employee_id % 2 != 0 AND       name NOT LIKE 'M%', salary, 0)) AS bonus  /*网路上另有case when的写法*/FROM EmployeesORDER BY employee_id;                           /*需要依employee_id才能过LeetCode*/

1873. Calculate Special Bonus

+-------------+----------+| Column Name | Type     |+-------------+----------+| id          | int      || name        | varchar  || sex         | ENUM     || salary      | int      |+-------------+----------+id is the primary key for this table.The sex column is ENUM value of type ('m', 'f').The table contains information about an employee.Write an SQL query to swap all 'f' and 'm' values (i.e., change all 'f' values to 'm' and vice versa) with a single update statement and no intermediate temporary tables.Note that you must write a single update statement, do not write any select statement for this problem.The query result format is in the following example.Example 1:Input: Salary table:+----+------+-----+--------+| id | name | sex | salary |+----+------+-----+--------+| 1  | A    | m   | 2500   || 2  | B    | f   | 1500   || 3  | C    | m   | 5500   || 4  | D    | f   | 500    |+----+------+-----+--------+Output: +----+------+-----+--------+| id | name | sex | salary |+----+------+-----+--------+| 1  | A    | f   | 2500   || 2  | B    | m   | 1500   || 3  | C    | f   | 5500   || 4  | D    | m   | 500    |+----+------+-----+--------+Explanation: (1, A) and (3, C) were changed from 'm' to 'f'.(2, B) and (4, D) were changed from 'f' to 'm'.

题目要我们将做性别调换,也就是把sex栏位的'f'、'm'两字互转,
下面我尝试了上一题查到的case when用法,
另外找到了更短的写法就一併附上。

/*UPDATE Salary  SET sex = CASE WHEN sex = 'm' THEN 'f' ELSE 'm' END;*/UPDATE Salary  SET sex = if(sex='m','f','m')  /*If sex栏='m'then改成'f', 不然else 'm'*/

196. Delete Duplicate Emails

+-------------+---------+| Column Name | Type    |+-------------+---------+| id          | int     || email       | varchar |+-------------+---------+id is the primary key column for this table.Each row of this table contains an email. The emails will not contain uppercase letters. Write an SQL query to delete all the duplicate emails, keeping only one unique email with the smallest id. Note that you are supposed to write a DELETE statement and not a SELECT one.After running your script, the answer shown is the Person table. The driver will first compile and run your piece of code and then show the Person table. The final order of the Person table does not matter.The query result format is in the following example. Example 1:Input: Person table:+----+------------------+| id | email            |+----+------------------+| 1  | john@example.com || 2  | bob@example.com  || 3  | john@example.com |+----+------------------+Output: +----+------------------+| id | email            |+----+------------------+| 1  | john@example.com || 2  | bob@example.com  |+----+------------------+Explanation: john@example.com is repeated two times. We keep the row with the smallest Id = 1.

这题需要删除重複的EMAIL并保留最小ID,题目有提示not a SELECT,
所以会先排除DISTINCT的用法,并到w3school看一下DELTEL的用法,
得知删除重複值的做法可以产生2个一样的表P1.P2,
再进一步比较EMAIL相同,与ID的大小,
由于脑袋到这有些打结,纠结在P1.id>P2.id的比法原理(为什么P1>P2?),
后来先说服自己他是从上往下去做比对,不知道这样理解的方向是不是有误。

DELETE P1FROM Person P1 ,Person P2    /*写Person AS P1也行,AS不一定要写*/WHERE P1.email=P2.email AND /*比较EMAIL跟ID大小*/        P1.id>P2.id;

**DAY02


关于作者: 网站小编

码农网专注IT技术教程资源分享平台,学习资源下载网站,58码农网包含计算机技术、网站程序源码下载、编程技术论坛、互联网资源下载等产品服务,提供原创、优质、完整内容的专业码农交流分享平台。

热门文章