公司企業(yè)網(wǎng)站的選擇seo專員工作內(nèi)容
文章目錄
- 175. 組合兩個表
- 181. 超過經(jīng)理收入的員工
- 182. 查找重復(fù)的電子郵箱
- COUNT(*)
- COUNT(*) 與 COUNT(column) 的區(qū)別
- where和vaing之間的區(qū)別用法
- 183.從不訂購的客戶
- 196.刪除重復(fù)的電子郵箱
- 197.上升的溫度
- 511.游戲玩法分析I
- 512.游戲玩法分析II
- 577.員工獎金
- 584.尋找用戶推薦人
- 586.訂單最多的客戶
- 595.大的國家
- 596.超過5名學(xué)生的課
- 603.連續(xù)空余座位(沒做出來)
- 607.銷售員
- 610.判斷三角形
- 613.直線上的最近距離
- 619.只出現(xiàn)一次的最大數(shù)字(做出一半)
- 620.有趣的電影
- 627.變更性別
- 1050.合作過至少三次的演員和導(dǎo)演
- 1068.產(chǎn)品銷售分析I
- 1069.產(chǎn)品銷售分析II
- 1075.項目員工I
- 1076.項目員工II(做出一半)
- 1082.銷售分析I
- 1083.銷售分析II
- 1084.銷售分析III
175. 組合兩個表
表: Person
+-------------+---------+
| 列名 | 類型 |
+-------------+---------+
| PersonId | int |
| FirstName | varchar |
| LastName | varchar |
+-------------+---------+
personId 是該表的主鍵（具有唯一值的列）。該表包含一些人的 ID 和他們的姓和名的信息。
表: Address
+-------------+---------+
| 列名 | 類型 |
+-------------+---------+
| AddressId | int |
| PersonId | int |
| City | varchar |
| State | varchar |
+-------------+---------+
addressId 是該表的主鍵（具有唯一值的列）。該表的每一行都包含一個 ID = PersonId 的人的城市和州的信息。
**編寫解決方案，報告 Person 表中每個人的姓、名、城市和州。如果 personId 的地址不在 Address 表中，則報告為 null 。**以 任意順序 返回結(jié)果表。結(jié)果格式如下所示。
示例 1:輸入: Person表:
+----------+----------+-----------+
| personId | lastName | firstName |
+----------+----------+-----------+
| 1 | Wang | Allen |
| 2 | Alice | Bob |
+----------+----------+-----------+
Address表:
+-----------+----------+---------------+------------+
| addressId | personId | city | state |
+-----------+----------+---------------+------------+
| 1 | 2 | New York City | New York |
| 2 | 3 | Leetcode | California |
+-----------+----------+---------------+------------+
輸出:
+-----------+----------+---------------+----------+
| firstName | lastName | city | state |
+-----------+----------+---------------+----------+
| Allen | Wang | Null | Null |
| Bob | Alice | New York City | New York |
+-----------+----------+---------------+----------+
解釋: 地址表中沒有 personId = 1 的地址，所以它們的城市和州返回 null。
addressId = 1 包含了 personId = 2 的地址信息。
解答如下
select firstName, lastName,city, state from Person left join Address onPerson.personId = Address.personId;
主要考察的是left join的使用
181. 超過經(jīng)理收入的員工
表：Employee
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| name | varchar |
| salary | int |
| managerId | int |
+-------------+---------+
id 是該表的主鍵（具有唯一值的列）。該表的每一行都表示雇員的ID、姓名、工資和經(jīng)理的ID。編寫解決方案，找出收入比經(jīng)理高的員工。以 任意順序 返回結(jié)果表。結(jié)果格式如下所示。
示例 1:輸入: Employee 表:
+----+-------+--------+-----------+
| id | name | salary | managerId |
+----+-------+--------+-----------+
| 1 | Joe | 70000 | 3 |
| 2 | Henry | 80000 | 4 |
| 3 | Sam | 60000 | Null |
| 4 | Max | 90000 | Null |
+----+-------+--------+-----------+
輸出:
+----------+
| Employee |
+----------+
| Joe |
+----------+
解釋: Joe 是唯一掙得比經(jīng)理多的雇員。
解答如下
select e1.name as Employee from Employee e1,Employee e2 where e1.ManagerId = e2.idand e1.Salary > e2.Salary;
考察笛卡爾積的用法
SELECT * FROM Employee e1, Employee e2;
生成的結(jié)果是 笛卡爾積（也叫 交叉連接 或 Cartesian Join）。這是兩張表之間的所有組合行的集合。
182. 查找重復(fù)的電子郵箱
表: Person
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| email | varchar |
+-------------+---------+
id 是該表的主鍵（具有唯一值的列）。此表的每一行都包含一封電子郵件。電子郵件不包含大寫字母。
編寫解決方案來報告所有重復(fù)的電子郵件。 請注意，可以保證電子郵件字段不為 NULL。以 任意順序 返回結(jié)果表。
結(jié)果格式如下例。
示例 1:輸入: Person 表:
+----+---------+
| id | email |
+----+---------+
| 1 | a@b.com |
| 2 | c@d.com |
| 3 | a@b.com |
+----+---------+
輸出:
+---------+
| Email |
+---------+
| a@b.com |
+---------+
解釋: a@b.com 出現(xiàn)了兩次。
解答如下
select email as Email from Person group by email having count(*) > 1;
考察對于count(*)和having的用法
COUNT(*)
COUNT(*) 會統(tǒng)計表中符合查詢條件的所有行的數(shù)量，包括 NULL 值的行。也就是說，它會對查詢結(jié)果中的每一行進(jìn)行計數(shù)，而不管這一行的具體列值是什么。
COUNT(*) 與 COUNT(column) 的區(qū)別
COUNT(*)：計算所有行數(shù)，不論列值是否為 NULL。
COUNT(column)：只計算某一列中非 NULL 值的行數(shù)。NULL 值不會被計算在內(nèi)。
where和vaing之間的區(qū)別用法
1. WHERE 與 HAVING 的區(qū)別
-
WHERE：用于過濾原始數(shù)據(jù)行，在 GROUP BY 之前執(zhí)行，也就是說，WHERE 子句無法使用聚合函數(shù)（如 COUNT()、SUM() 等）。它只能應(yīng)用在數(shù)據(jù)的原始列上，不能用于過濾基于分組后的結(jié)果。 -
HAVING：font color=“red”>用于過濾 GROUP BY 分組后的結(jié)果，允許你基于聚合函數(shù)的結(jié)果進(jìn)行篩選。HAVING 在 GROUP BY 之后執(zhí)行，因此你可以使用 COUNT() 這樣的聚合函數(shù)。
183.從不訂購的客戶
Customers 表：
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| name | varchar |
+-------------+---------+
在 SQL 中，id 是該表的主鍵。該表的每一行都表示客戶的 ID 和名稱。
Orders 表：
+-------------+------+
| Column Name | Type |
+-------------+------+
| id | int |
| customerId | int |
+-------------+------+
在 SQL 中，id 是該表的主鍵。customerId 是 Customers 表中 ID 的外鍵( Pandas 中的連接鍵)。該表的每一行都表示訂單的 ID 和訂購該訂單的客戶的 ID。找出所有從不點任何東西的顧客。以 任意順序 返回結(jié)果表。結(jié)果格式如下所示。
示例 1：輸入：Customers table:
+----+-------+
| id | name |
+----+-------+
| 1 | Joe |
| 2 | Henry |
| 3 | Sam |
| 4 | Max |
+----+-------+
Orders table:
+----+------------+
| id | customerId |
+----+------------+
| 1 | 3 |
| 2 | 1 |
+----+------------+
輸出：
+-----------+
| Customers |
+-----------+
| Henry |
| Max |
+-----------+
解答如下
select name as Customers from customers left join Orders on customers.id = Orders.customerid where Orders.customerid is NULL;
本題考察 對于空值的用法
在 MySQL 中，要篩選某一列為空 (NULL) 的值，可以使用 IS NULL 來進(jìn)行篩選。
NULL 與空字符串或零不同：NULL 表示沒有值，而空字符串 (‘’) 或零 (0) 是有效的值，IS NULL 只會篩選出那些確實沒有值的記錄。
IS NULL 與 = 的區(qū)別：在 MySQL 中，NULL 是特殊的，不能直接用 = 比較。因此，不能使用 WHERE column_name = NULL，而是要使用 WHERE column_name IS NULL。
196.刪除重復(fù)的電子郵箱
表: Person
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| email | varchar |
+-------------+---------+
id 是該表的主鍵列(具有唯一值的列)。該表的每一行包含一封電子郵件。電子郵件將不包含大寫字母。
編寫解決方案 刪除 所有重復(fù)的電子郵件，只保留一個具有最小 id 的唯一電子郵件。
對于 SQL 用戶，請注意你應(yīng)該編寫一個 DELETE 語句而不是 SELECT 語句。）
對于 Pandas 用戶，請注意你應(yīng)該直接修改 Person 表。）
運行腳本后，顯示的答案是 Person 表。驅(qū)動程序?qū)⑹紫染幾g并運行您的代碼片段，然后再顯示 Person 表。Person 表的最終順序 無關(guān)緊要 。
返回結(jié)果格式如下示例所示。
示例 1:輸入: Person 表:
+----+------------------+
| id | email |
+----+------------------+
| 1 | john@example.com |
| 2 | bob@example.com |
| 3 | john@example.com |
+----+------------------+
輸出:
+----+------------------+
| id | email |
+----+------------------+
| 1 | john@example.com |
| 2 | bob@example.com |
+----+------------------+
解釋: john@example.com重復(fù)兩次。我們保留最小的Id = 1。
解答如下
delete from Person where id not in (select * from (select min(id) from Person GROUP BY email) t);
考察在delete中進(jìn)行查詢，以及派生表的用法
在 DELETE 或 UPDATE 操作中，你不能同時對一個表進(jìn)行修改并在子查詢中查詢該表.換句話說，你不能在 DELETE 語句中修改 Person 表，同時在 FROM 子查詢中查詢 Person 表。
我們需要用一個額外的嵌套子查詢（也就是派生表）來規(guī)避這個限制，t 是派生表（子查詢結(jié)果）的別名，雖然這個別名在查詢中沒有進(jìn)一步的使用，但它是 MySQL 的一個要求，每個子查詢都必須有別名。通過使用派生表，可以避免 MySQL 中同時修改和查詢同一個表時產(chǎn)生的錯誤。
197.上升的溫度
表： Weather
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| recordDate | date |
| temperature | int |
+---------------+---------+
id 是該表具有唯一值的列。沒有具有相同 recordDate 的不同行。該表包含特定日期的溫度信息
編寫解決方案，找出與之前（昨天的）日期相比溫度更高的所有日期的 id 。返回結(jié)果 無順序要求 。
結(jié)果格式如下例子所示。
示例 1：輸入：Weather 表：
+----+------------+-------------+
| id | recordDate | Temperature |
+----+------------+-------------+
| 1 | 2015-01-01 | 10 |
| 2 | 2015-01-02 | 25 |
| 3 | 2015-01-03 | 20 |
| 4 | 2015-01-04 | 30 |
+----+------------+-------------+
輸出：
+----+
| id |
+----+
| 2 |
| 4 |
+----+
解釋：
2015-01-02 的溫度比前一天高（10 -> 25）
2015-01-04 的溫度比前一天高（20 -> 30）
結(jié)果輸出
select w1.id from weather w1,weather w2 where DATEDIFF(w1.recordDate,w2.recordDate) = 1 and w1.temperature > w2.temperature;
考察笛卡爾積和DATADIFF函數(shù)用法
DATEDIFF() 是 MySQL 中的一個函數(shù)，用于計算兩個日期之間的差值，以天數(shù)表示。
語法
DATEDIFF(date1, date2)
- date1 和 date2 是兩個日期表達(dá)式。DATEDIFF 函數(shù)會計算 date1 - date2，并返回相差的天數(shù)。
- 結(jié)果為整數(shù)，正數(shù)表示 date1 晚于 date2，負(fù)數(shù)表示 date1 早于 date2。
示例
1.計算兩個具體日期之間的差值：
SELECT DATEDIFF('2024-09-17', '2024-09-10') AS days_diff;sqldays_diff
7
2.計算某個日期與當(dāng)前日期之間的差值：
SELECT DATEDIFF(CURDATE(), '2023-01-01') AS days_diff;
假設(shè)當(dāng)前日期為 2024-09-17，則輸出結(jié)果為：
days_diff
625
3.在表中使用 DATEDIFF 進(jìn)行篩選： 假設(shè)有一個表 orders，其中有一列 order_date，你可以查詢所有距離當(dāng)前日期超過 30 天的訂單：
SELECT *
FROM orders
WHERE DATEDIFF(CURDATE(), order_date) > 30;
注意
DATEDIFF() 返回結(jié)果只計算天數(shù)，不考慮時間（時、分、秒）。如果想要精確到時間，可以使用 TIMESTAMPDIFF() 函數(shù)。
511.游戲玩法分析I
活動表 Activity：
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| player_id | int |
| device_id | int |
| event_date | date |
| games_played | int |
+--------------+---------+
在 SQL 中，表的主鍵是 (player_id, event_date)。這張表展示了一些游戲玩家在游戲平臺上的行為活動。
每行數(shù)據(jù)記錄了一名玩家在退出平臺之前，當(dāng)天使用同一臺設(shè)備登錄平臺后打開的游戲的數(shù)目（可能是 0 個）。
查詢每位玩家 第一次登錄平臺的日期。查詢結(jié)果的格式如下所示：
Activity 表：
+-----------+-----------+------------+--------------+
| player_id | device_id | event_date | games_played |
+-----------+-----------+------------+--------------+
| 1 | 2 | 2016-03-01 | 5 |
| 1 | 2 | 2016-05-02 | 6 |
| 2 | 3 | 2017-06-25 | 1 |
| 3 | 1 | 2016-03-02 | 0 |
| 3 | 4 | 2018-07-03 | 5 |
+-----------+-----------+------------+--------------+
Result 表：
+-----------+-------------+
| player_id | first_login |
+-----------+-------------+
| 1 | 2016-03-01 |
| 2 | 2017-06-25 |
| 3 | 2016-03-02 |
+-----------+-------------+
結(jié)果如下
select player_id,min(event_date) as first_login from activity GROUP BY player_id;
考察日期之間比較使用min或者max方法
512.游戲玩法分析II
able: Activity
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| player_id | int |
| device_id | int |
| event_date | date |
| games_played | int |
+--------------+---------+
(player_id, event_date) 是這個表的兩個主鍵(具有唯一值的列的組合)
這個表顯示的是某些游戲玩家的游戲活動情況每一行是在某天使用某個設(shè)備登出之前登錄并玩多個游戲（可能為0）的玩家的記錄。請編寫解決方案，描述每一個玩家首次登陸的設(shè)備名稱
返回結(jié)果格式如以下示例：
示例 1：輸入：Activity table:
+-----------+-----------+------------+--------------+
| player_id | device_id | event_date | games_played |
+-----------+-----------+------------+--------------+
| 1 | 2 | 2016-03-01 | 5 |
| 1 | 2 | 2016-05-02 | 6 |
| 2 | 3 | 2017-06-25 | 1 |
| 3 | 1 | 2016-03-02 | 0 |
| 3 | 4 | 2018-07-03 | 5 |
+-----------+-----------+------------+--------------+
輸出：
+-----------+-----------+
| player_id | device_id |
+-----------+-----------+
| 1 | 2 |
| 2 | 3 |
| 3 | 1 |
+-----------+-----------+
結(jié)果如下
select t.player_id, activity.device_id from (select player_id,min(event_date) as early_date from activity GROUP BY player_id) as t left join activity on t.player_id = activity.player_id and t.early_date = activity.event_date ;
考察子查詢語句，嵌套查詢，聯(lián)合查詢
別人做法（聯(lián)合查詢）
select player_id, device_id from activitywhere (player_id, event_date) in (select player_id, min(event_date) from activity group by player_id)
577.員工獎金
表：Employee
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| empId | int |
| name | varchar |
| supervisor | int |
| salary | int |
+-------------+---------+
empId 是該表中具有唯一值的列。
該表的每一行都表示員工的姓名和 id，以及他們的工資和經(jīng)理的 id。
表：Bonus
+-------------+------+
| Column Name | Type |
+-------------+------+
| empId | int |
| bonus | int |
+-------------+------+
empId 是該表具有唯一值的列。empId 是 Employee 表中 empId 的外鍵(reference 列)。
該表的每一行都包含一個員工的 id 和他們各自的獎金。編寫解決方案，報告每個獎金 少于 1000 的員工的姓名和獎金數(shù)額。以 任意順序 返回結(jié)果表。結(jié)果格式如下所示。
示例 1：輸入：Employee table:
+-------+--------+------------+--------+
| empId | name | supervisor | salary |
+-------+--------+------------+--------+
| 3 | Brad | null | 4000 |
| 1 | John | 3 | 1000 |
| 2 | Dan | 3 | 2000 |
| 4 | Thomas | 3 | 4000 |
+-------+--------+------------+--------+
Bonus table:
+-------+-------+
| empId | bonus |
+-------+-------+
| 2 | 500 |
| 4 | 2000 |
+-------+-------+
輸出：
+------+-------+
| name | bonus |
+------+-------+
| Brad | null |
| John | null |
| Dan | 500 |
+------+-------+
結(jié)果如下
select name,bonus from employee e1 LEFT JOIN bonus on e1.empId = bonus.empId where bonus is null or bonus < 1000;
考察表連接和null用法
584.尋找用戶推薦人
表: Customer
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| name | varchar |
| referee_id | int |
+-------------+---------+
在 SQL 中，id 是該表的主鍵列。該表的每一行表示一個客戶的 id、姓名以及推薦他們的客戶的 id。找出那些 沒有被 id = 2 的客戶 推薦 的客戶的姓名。以 任意順序 返回結(jié)果表。結(jié)果格式如下所示。
示例 1：輸入： Customer 表:
+----+------+------------+
| id | name | referee_id |
+----+------+------------+
| 1 | Will | null |
| 2 | Jane | null |
| 3 | Alex | 2 |
| 4 | Bill | null |
| 5 | Zack | 1 |
| 6 | Mark | 2 |
+----+------+------------+
輸出：
+------+
| name |
+------+
| Will |
| Jane |
| Bill |
| Zack |
+------+
結(jié)果如下
select name from customer where referee_id !=2 or referee_id is null;
這道題的考察點在于，sql里面的不等于，不包含null。
586.訂單最多的客戶
表: Orders
+-----------------+----------+
| Column Name | Type |
+-----------------+----------+
| order_number | int |
| customer_number | int |
+-----------------+----------+
在 SQL 中，Order_number是該表的主鍵。此表包含關(guān)于訂單ID和客戶ID的信息。查找下了 最多訂單 的客戶的 customer_number 。測試用例生成后， 恰好有一個客戶 比任何其他客戶下了更多的訂單。查詢結(jié)果格式如下所示。
示例 1:輸入: Orders 表:
+--------------+-----------------+
| order_number | customer_number |
+--------------+-----------------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 3 |
+--------------+-----------------+
輸出:
+-----------------+
| customer_number |
+-----------------+
| 3 |
+-----------------+
解釋: customer_number 為 ‘3’ 的顧客有兩個訂單，比顧客 ‘1’ 或者 ‘2’ 都要多，因為他們只有一個訂單。所以結(jié)果是該顧客的 customer_number ，也就是 3 。
結(jié)果如下：
select customer_number from (select customer_number,count(*) as count from orders GROUP BY customer_number ORDER BY count desc) as t LIMIT 1;
595.大的國家
World 表：
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| name | varchar |
| continent | varchar |
| area | int |
| population | int |
| gdp | bigint |
+-------------+---------+
name 是該表的主鍵（具有唯一值的列）。這張表的每一行提供：國家名稱、所屬大陸、面積、人口和 GDP 值。
如果一個國家滿足下述兩個條件之一，則認(rèn)為該國是 大國 ：面積至少為 300 萬平方公里（即，3000000 km2），或者人口至少為 2500 萬（即 25000000）編寫解決方案找出 大國 的國家名稱、人口和面積。
按 任意順序 返回結(jié)果表。返回結(jié)果格式如下例所示。
示例：輸入：World 表：
+-------------+-----------+---------+------------+--------------+
| name | continent | area | population | gdp |
+-------------+-----------+---------+------------+--------------+
| Afghanistan | Asia | 652230 | 25500100 | 20343000000 |
| Albania | Europe | 28748 | 2831741 | 12960000000 |
| Algeria | Africa | 2381741 | 37100000 | 188681000000 |
| Andorra | Europe | 468 | 78115 | 3712000000 |
| Angola | Africa | 1246700 | 20609294 | 100990000000 |
+-------------+-----------+---------+------------+--------------+
輸出：
+-------------+------------+---------+
| name | population | area |
+-------------+------------+---------+
| Afghanistan | 25500100 | 652230 |
| Algeria | 37100000 | 2381741 |
+-------------+------------+---------+
結(jié)果如下：
select name ,population, area
from World
where population >= 25000000 or `area` >= 3000000
596.超過5名學(xué)生的課
表: Courses
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| student | varchar |
| class | varchar |
+-------------+---------+
(student, class)是該表的主鍵（不同值的列的組合）。該表的每一行表示學(xué)生的名字和他們注冊的班級。
查詢 至少有 5 個學(xué)生 的所有班級。以 任意順序 返回結(jié)果表。結(jié)果格式如下所示。
示例 1:輸入: Courses table:
+---------+----------+
| student | class |
+---------+----------+
| A | Math |
| B | English |
| C | Math |
| D | Biology |
| E | Math |
| F | Computer |
| G | Math |
| H | Math |
| I | Math |
+---------+----------+
輸出:
+---------+
| class |
+---------+
| Math |
+---------+
解釋:
-數(shù)學(xué)課有 6 個學(xué)生，所以我們包括它。
-英語課有 1 名學(xué)生，所以我們不包括它。
-生物課有 1 名學(xué)生，所以我們不包括它。
-計算機課有 1 個學(xué)生，所以我們不包括它。
結(jié)果如下
select class from courses GROUP BY class having count(*) >=5;
603.連續(xù)空余座位(沒做出來)
表: Cinema
+-------------+------+
| Column Name | Type |
+-------------+------+
| seat_id | int |
| free | bool |
+-------------+------+
Seat_id 是該表的自動遞增主鍵列。在 PostgreSQL 中，free 存儲為整數(shù)。請使用 ::boolean 將其轉(zhuǎn)換為布爾格式。該表的每一行表示第 i 個座位是否空閑。1 表示空閑，0 表示被占用。查找電影院所有連續(xù)可用的座位。
返回按 seat_id 升序排序 的結(jié)果表。測試用例的生成使得兩個以上的座位連續(xù)可用。結(jié)果表格式如下所示。
示例 1:輸入: Cinema 表:
+---------+------+
| seat_id | free |
+---------+------+
| 1 | 1 |
| 2 | 0 |
| 3 | 1 |
| 4 | 1 |
| 5 | 1 |
+---------+------+
輸出:
+---------+
| seat_id |
+---------+
| 3 |
| 4 |
| 5 |
+---------+
解題如下
select DISTINCT c1.seat_id from cinema c1,cinema c2 where abs(c2.seat_id - c1.seat_id) = 1 ANDc1.free = 1 and c2.free = 1 ORDER BY c1.seat_id;
考察笛卡爾積用法
607.銷售員
表: SalesPerson
+-----------------+---------+
| Column Name | Type |
+-----------------+---------+
| sales_id | int |
| name | varchar |
| salary | int |
| commission_rate | int |
| hire_date | date |
+-----------------+---------+
sales_id 是該表的主鍵列(具有唯一值的列)。該表的每一行都顯示了銷售人員的姓名和 ID ，以及他們的工資、傭金率和雇傭日期。
表: Company
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| com_id | int |
| name | varchar |
| city | varchar |
+-------------+---------+
com_id 是該表的主鍵列(具有唯一值的列)。該表的每一行都表示公司的名稱和 ID ，以及公司所在的城市。
表: Orders
+-------------+------+
| Column Name | Type |
+-------------+------+
| order_id | int |
| order_date | date |
| com_id | int |
| sales_id | int |
| amount | int |
+-------------+------+
order_id 是該表的主鍵列(具有唯一值的列)。com_id 是 Company 表中 com_id 的外鍵（reference 列）。
sales_id 是來自銷售員表 sales_id 的外鍵（reference 列）。該表的每一行包含一個訂單的信息。這包括公司的 ID 、銷售人員的 ID 、訂單日期和支付的金額。
編寫解決方案，找出沒有任何與名為 “RED” 的公司相關(guān)的訂單的所有銷售人員的姓名。以 任意順序 返回結(jié)果表。返回結(jié)果格式如下所示。
結(jié)果如下
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"));
610.判斷三角形
表: Triangle
+-------------+------+
| Column Name | Type |
+-------------+------+
| x | int |
| y | int |
| z | int |
+-------------+------+
在 SQL 中，(x, y, z)是該表的主鍵列。該表的每一行包含三個線段的長度。對每三個線段報告它們是否可以形成一個三角形。以 任意順序 返回結(jié)果表。查詢結(jié)果格式如下所示。
示例 1:輸入: Triangle 表:
+----+----+----+
| x | y | z |
+----+----+----+
| 13 | 15 | 30 |
| 10 | 20 | 15 |
+----+----+----+
輸出:
+----+----+----+----------+
| x | y | z | triangle |
+----+----+----+----------+
| 13 | 15 | 30 | No |
| 10 | 20 | 15 | Yes |
+----+----+----+----------+
結(jié)果如下
select x,y,z,CASE WHEN x+y >z and abs(x-y) <z THEN 'Yes'ELSE 'NO'END AS 'triangle'from triangle;
或者
select x,y,z, if(x+y>z and abs(x-y)<z) as triangle from Triangle;
考察點
- 不知道如何生成新的一列
- if或者CASE都可以
613.直線上的最近距離
表： Point
+-------------+------+
| Column Name | Type |
+-------------+------+
| x | int |
+-------------+------+
在SQL中，x是該表的主鍵列。該表的每一行表示X軸上一個點的位置。找到 Point 表中任意兩點之間的最短距離。返回結(jié)果格式如下例所示。
示例 1：輸入：Point 表:
+----+
| x |
+----+
| -1 |
| 0 |
| 2 |
+----+
輸出：
+----------+
| shortest |
+----------+
| 1 |
+----------+
解釋：點 -1 和 0 之間的最短距離為 |(-1) - 0| = 1。
結(jié)果如下：
select abs(p1.x-p2.x) as shortest from point p1,point p2 where abs(p1.x-p2.x) > 0 ORDER BY shortest limit 1;
考察點：大殺器，笛卡爾積
619.只出現(xiàn)一次的最大數(shù)字(做出一半)
MyNumbers 表：
+-------------+------+
| Column Name | Type |
+-------------+------+
| num | int |
+-------------+------+
該表可能包含重復(fù)項（換句話說，在SQL中，該表沒有主鍵）。這張表的每一行都含有一個整數(shù)。
單一數(shù)字 是在 MyNumbers 表中只出現(xiàn)一次的數(shù)字。找出最大的 單一數(shù)字 。如果不存在 單一數(shù)字 ，則返回 null 。查詢結(jié)果如下例所示。
示例 1：輸入：MyNumbers 表：
+-----+
| num |
+-----+
| 8 |
| 8 |
| 3 |
| 3 |
| 1 |
| 4 |
| 5 |
| 6 |
+-----+
輸出：
+-----+
| num |
+-----+
| 6 |
+-----+
解釋：單一數(shù)字有 1、4、5 和 6 。6 是最大的單一數(shù)字，返回 6 。
示例 2：輸入：MyNumbers table:
+-----+
| num |
+-----+
| 8 |
| 8 |
| 7 |
| 7 |
| 3 |
| 3 |
| 3 |
+-----+
輸出：
+------+
| num |
+------+
| null |
+------+
解釋：輸入的表中不存在單一數(shù)字，所以返回 null 。
結(jié)果如下
select (select num from mynumbers group by num having count(num) = 1 order by num desc limit 1) num ;
考察：如何對NULL進(jìn)行處理。利用選中空表返回null的特性
620.有趣的電影
表：cinema
+----------------+----------+
| Column Name | Type |
+----------------+----------+
| id | int |
| movie | varchar |
| description | varchar |
| rating | float |
+----------------+----------+
id 是該表的主鍵(具有唯一值的列)。每行包含有關(guān)電影名稱、類型和評級的信息。評級為 [0,10] 范圍內(nèi)的小數(shù)點后 2 位浮點數(shù)。
編寫解決方案，找出所有影片描述為 非 boring (不無聊) 的并且 id 為奇數(shù) 的影片。返回結(jié)果按 rating 降序排列。結(jié)果格式如下示例。
示例 1：輸入：
+---------+-----------+--------------+-----------+
| id | movie | description | rating |
+---------+-----------+--------------+-----------+
| 1 | War | great 3D | 8.9 |
| 2 | Science | fiction | 8.5 |
| 3 | irish | boring | 6.2 |
| 4 | Ice song | Fantacy | 8.6 |
| 5 | House card| Interesting| 9.1 |
+---------+-----------+--------------+-----------+
輸出：
+---------+-----------+--------------+-----------+
| id | movie | description | rating |
+---------+-----------+--------------+-----------+
| 5 | House card| Interesting| 9.1 |
| 1 | War | great 3D | 8.9 |
+---------+-----------+--------------+-----------+
解釋：
我們有三部電影，它們的 id 是奇數(shù):1、3 和 5。id = 3 的電影是 boring 的，所以我們不把它包括在答案中。
結(jié)果如下：
select * from cinema where description != "boring" and id % 2 != 0 ORDER BY rating desc;
627.變更性別
Salary 表：
+-------------+----------+
| Column Name | Type |
+-------------+----------+
| id | int |
| name | varchar |
| sex | ENUM |
| salary | int |
+-------------+----------+
id 是這個表的主鍵（具有唯一值的列）。sex 這一列的值是 ENUM 類型，只能從 (‘m’, ‘f’) 中取。本表包含公司雇員的信息。
請你編寫一個解決方案來交換所有的 ‘f’ 和 ‘m’ （即，將所有 ‘f’ 變?yōu)?‘m’ ，反之亦然），僅使用 單個 update 語句 ，且不產(chǎn)生中間臨時表。注意，你必須僅使用一條 update 語句，且 不能 使用 select 語句。結(jié)果如下例所示。
示例 1:輸入：Salary 表：
+----+------+-----+--------+
| id | name | sex | salary |
+----+------+-----+--------+
| 1 | A | m | 2500 |
| 2 | B | f | 1500 |
| 3 | C | m | 5500 |
| 4 | D | f | 500 |
+----+------+-----+--------+
輸出：
+----+------+-----+--------+
| id | name | sex | salary |
+----+------+-----+--------+
| 1 | A | f | 2500 |
| 2 | B | m | 1500 |
| 3 | C | f | 5500 |
| 4 | D | m | 500 |
+----+------+-----+--------+
解釋：
(1, A) 和 (3, C) 從 ‘m’ 變?yōu)?‘f’ 。
(2, B) 和 (4, D) 從 ‘f’ 變?yōu)?‘m’ 。
結(jié)果如下
update salaryset sex = case when sex='m' then 'f'when sex='f' then 'm'
end
考察點
- UPDATE語句使用
UPDATE 語句的基本格式如下：
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
- CASE語句用法
在 MySQL 中，CASE 語句用于在 SELECT、UPDATE、INSERT、DELETE 語句中根據(jù)條件返回不同的結(jié)果。它有兩種常用的語法形式：簡單 CASE 和 搜索 CASE。
1. 簡單 CASE 語法
簡單 CASE 語句根據(jù)表達(dá)式的值進(jìn)行匹配。
語法：
CASE expressionWHEN value1 THEN result1WHEN value2 THEN result2...ELSE default_result
END
- expression 是要比較的值。
- WHEN 是條件語句，比較 expression 和 value。
- THEN 后面是 WHEN 條件為真時返回的結(jié)果。
- ELSE 提供一個默認(rèn)的返回值（可選）。
- END 是 CASE 語句的結(jié)束。
示例：
假設(shè)有一張名為 students 的表，包含 id 和 score 列。你想要根據(jù) score 返回等級。
SELECT id, score, CASE scoreWHEN 90 THEN 'A'WHEN 80 THEN 'B'WHEN 70 THEN 'C'ELSE 'F'END AS grade
FROM students;
這個查詢將根據(jù) score 列的值，返回相應(yīng)的等級。
2. 搜索 CASE 語法
搜索 CASE 語句基于布爾表達(dá)式進(jìn)行條件判斷。
CASE WHEN condition1 THEN result1WHEN condition2 THEN result2...ELSE default_result
END
- WHEN 后面的 condition 是布爾表達(dá)式。
- THEN 后面是條件為真時返回的結(jié)果。
- ELSE 提供一個默認(rèn)的返回值（可選）。
- END 是 CASE 語句的結(jié)束。
示例：
假設(shè)有一個員工表 employees，你想根據(jù)員工的工資水平來分類：
SELECT id, name, salary,CASE WHEN salary >= 10000 THEN 'High'WHEN salary >= 5000 THEN 'Medium'ELSE 'Low'END AS salary_level
FROM employees;
這個查詢會根據(jù)員工的 salary 來分類為 ‘High’、‘Medium’ 或 ‘Low’。
3. 在 UPDATE 語句中使用 CASE
你也可以在 UPDATE 語句中使用 CASE，根據(jù)條件更新不同的值。
示例：
更新員工表，根據(jù)不同的職位更新不同的工資：
UPDATE employees
SET salary = CASE WHEN position = 'Manager' THEN 8000WHEN position = 'Developer' THEN 6000ELSE 4000END;
這條語句將根據(jù)員工的職位更新不同的工資。CASE 語句可以用于條件查詢和更新。它有兩種形式：簡單 CASE（根據(jù)值進(jìn)行匹配）和 搜索 CASE（根據(jù)條件進(jìn)行匹配）。CASE 語句可以在 SELECT、UPDATE 等多種 SQL 操作中使用。
1050.合作過至少三次的演員和導(dǎo)演
ActorDirector 表：
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| actor_id | int |
| director_id | int |
| timestamp | int |
+-------------+---------+
timestamp 是這張表的主鍵(具有唯一值的列).編寫解決方案找出合作過至少三次的演員和導(dǎo)演的 id 對 (actor_id, director_id)
示例 1：輸入：ActorDirector 表：
+-------------+-------------+-------------+
| actor_id | director_id | timestamp |
+-------------+-------------+-------------+
| 1 | 1 | 0 |
| 1 | 1 | 1 |
| 1 | 1 | 2 |
| 1 | 2 | 3 |
| 1 | 2 | 4 |
| 2 | 1 | 5 |
| 2 | 1 | 6 |
+-------------+-------------+-------------+
輸出：
+-------------+-------------+
| actor_id | director_id |
+-------------+-------------+
| 1 | 1 |
+-------------+-------------+
解釋：
唯一的 id 對是 (1, 1)，他們恰好合作了 3 次。
結(jié)果如下：
select actor_id,director_id from actordirector GROUP BY actor_id,director_id having count(*) >= 3;
考點：group by用法
1068.產(chǎn)品銷售分析I
銷售表 Sales：
+-------------+-------+
| Column Name | Type |
+-------------+-------+
| sale_id | int |
| product_id | int |
| year | int |
| quantity | int |
| price | int |
+-------------+-------+
(sale_id, year) 是銷售表 Sales 的主鍵（具有唯一值的列的組合）。product_id 是關(guān)聯(lián)到產(chǎn)品表 Product 的外鍵（reference 列）。該表的每一行顯示 product_id 在某一年的銷售情況。
注意: price 表示每單位價格。
產(chǎn)品表 Product：
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| product_id | int |
| product_name | varchar |
+--------------+---------+
product_id 是表的主鍵（具有唯一值的列）。該表的每一行表示每種產(chǎn)品的產(chǎn)品名稱。編寫解決方案，以獲取 Sales 表中所有 sale_id 對應(yīng)的 product_name 以及該產(chǎn)品的所有 year 和 price 。返回結(jié)果表 無順序要求 。
結(jié)果格式示例如下。
示例 1：輸入：Sales 表：
+---------+------------+------+----------+-------+
| sale_id | product_id | year | quantity | price |
+---------+------------+------+----------+-------+
| 1 | 100 | 2008 | 10 | 5000 |
| 2 | 100 | 2009 | 12 | 5000 |
| 7 | 200 | 2011 | 15 | 9000 |
+---------+------------+------+----------+-------+
Product 表：
+------------+--------------+
| product_id | product_name |
+------------+--------------+
| 100 | Nokia |
| 200 | Apple |
| 300 | Samsung |
+------------+--------------+
輸出：
+--------------+-------+-------+
| product_name | year | price |
+--------------+-------+-------+
| Nokia | 2008 | 5000 |
| Nokia | 2009 | 5000 |
| Apple | 2011 | 9000 |
+--------------+-------+-------+
結(jié)果如下
select product.product_name,sales.year,sales.price from sales LEFT JOIN product on sales.product_id = product.product_id;
1069.產(chǎn)品銷售分析II
銷售表：Sales
+-------------+-------+
| Column Name | Type |
+-------------+-------+
| sale_id | int |
| product_id | int |
| year | int |
| quantity | int |
| price | int |
+-------------+-------+
sale_id 是這個表的主鍵（具有唯一值的列）。product_id 是 Product 表的外鍵（reference 列）。該表的每一行顯示產(chǎn)品product_id在某一年的銷售情況。請注意價格是每單位的。
產(chǎn)品表：Product
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| product_id | int |
| product_name | varchar |
+--------------+---------+
product_id 是這個表的主鍵（具有唯一值的列）。該表的每一行表示每種產(chǎn)品的產(chǎn)品名稱。
編寫解決方案，統(tǒng)計每個產(chǎn)品的銷售總量。返回結(jié)果表 無順序要求 。結(jié)果格式如下例子所示。
示例 1：輸入：Sales 表：
+---------+------------+------+----------+-------+
| sale_id | product_id | year | quantity | price |
+---------+------------+------+----------+-------+
| 1 | 100 | 2008 | 10 | 5000 |
| 2 | 100 | 2009 | 12 | 5000 |
| 7 | 200 | 2011 | 15 | 9000 |
+---------+------------+------+----------+-------+
Product 表：
+------------+--------------+
| product_id | product_name |
+------------+--------------+
| 100 | Nokia |
| 200 | Apple |
| 300 | Samsung |
+------------+--------------+
輸出：
+--------------+----------------+
| product_id | total_quantity |
+--------------+----------------+
| 100 | 22 |
| 200 | 15 |
+--------------+----------------+
結(jié)果如下
select sales.product_id,sum(quantity) as total_quantity from sales LEFT JOIN product on sales.product_id = product.product_id GROUP BY product_id;
1075.項目員工I
項目表 Project：
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| project_id | int |
| employee_id | int |
+-------------+---------+
主鍵為 (project_id, employee_id)。employee_id 是員工表 Employee 表的外鍵。這張表的每一行表示 employee_id 的員工正在 project_id 的項目上工作。
員工表 Employee：
+------------------+---------+
| Column Name | Type |
+------------------+---------+
| employee_id | int |
| name | varchar |
| experience_years | int |
+------------------+---------+
主鍵是 employee_id。數(shù)據(jù)保證 experience_years 非空。這張表的每一行包含一個員工的信息。
請寫一個 SQL 語句，查詢每一個項目中員工的 平均 工作年限，精確到小數(shù)點后兩位。以 任意 順序返回結(jié)果表。
查詢結(jié)果的格式如下。
示例 1:輸入：Project 表：
+-------------+-------------+
| project_id | employee_id |
+-------------+-------------+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 4 |
+-------------+-------------+
Employee 表：
+-------------+--------+------------------+
| employee_id | name | experience_years |
+-------------+--------+------------------+
| 1 | Khaled | 3 |
| 2 | Ali | 2 |
| 3 | John | 1 |
| 4 | Doe | 2 |
+-------------+--------+------------------+
輸出：
+-------------+---------------+
| project_id | average_years |
+-------------+---------------+
| 1 | 2.00 |
| 2 | 2.50 |
+-------------+---------------+
解釋：第一個項目中，員工的平均工作年限是 (3 + 2 + 1) / 3 = 2.00；第二個項目中，員工的平均工作年限是 (3 + 2) / 2 = 2.50
結(jié)果如下：
select project_id,round(AVG(experience_years),2) as average_years from Project LEFT JOIN Employee on Project.employee_id = employee.employee_id GROUP BY project_id;
1076.項目員工II(做出一半)
表：Project
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| project_id | int |
| employee_id | int |
+-------------+---------+
(project_id, employee_id) 是該表的主鍵(具有唯一值的列的組合)。employee_id 是該表的外鍵(reference 列)。
該表的每一行都表明 employee_id 的雇員正在處理 Project 表中 project_id 的項目。
表：Employee
+------------------+---------+
| Column Name | Type |
+------------------+---------+
| employee_id | int |
| name | varchar |
| experience_years | int |
+------------------+---------+
employee_id 是該表的主鍵(具有唯一值的列)。該表的每一行都包含一名雇員的信息。
編寫一個解決方案來報告所有擁有最多員工的 項目。以 任意順序 返回結(jié)果表。返回結(jié)果格式如下所示。
示例 1：輸入：Project table:
+-------------+-------------+
| project_id | employee_id |
+-------------+-------------+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 4 |
+-------------+-------------+
Employee table:
+-------------+--------+------------------+
| employee_id | name | experience_years |
+-------------+--------+------------------+
| 1 | Khaled | 3 |
| 2 | Ali | 2 |
| 3 | John | 1 |
| 4 | Doe | 2 |
+-------------+--------+------------------+
輸出：
+-------------+
| project_id |
+-------------+
| 1 |
+-------------+
解釋：第一個項目有3名員工，第二個項目有2名員工。
難點在于最多的項目重復(fù)值可能有多個
解答1如下，考察公共表達(dá)式用法
with t as (
select project_id,count(*) as count from Project LEFT JOIN Employee on Project.employee_id = employee.employee_id GROUP BY project_id ORDER BY count desc )select project_id from t where count = (select max(count) from t);
解釋：
- CTE (
WITH t AS)：定義了一個公共表表達(dá)式 t，其計算每個 project_id 的員工數(shù)量，并使用 LEFT JOIN 來處理沒有員工的項目。 - MAX(count) 子查詢：在外層查詢中，通過一個子查詢計算出 t 中 count 列的最大值。
- 外層查詢：外層查詢將 count 等于最大值的 project_id 選擇出來。
MySQL 處理方式
- MySQL 會先處理 CTE t，生成一個臨時表，包含每個 project_id 及其員工數(shù)量。然后，通過子查詢計算出 count 的最大值，最終從 CTE t 中選擇 count 等于最大值的項目。
注意點：
- WITH 語句從 MySQL 8.0 開始支持。
- 如果有多個 project_id 的員工數(shù)相同且是最大值，都會被選中。
解答如2，考察ALL的用法
select project_id from Project GROUP BY project_id HAVING COUNT(project_id) >= ALL(select count(project_id) from project GROUP BY project_id);
在 MySQL 中，ALL 運算符用于比較某個表達(dá)式與子查詢返回的所有值。
ALL 的典型用法是用于與 =, >, <, >=, <=, != 等比較運算符結(jié)合使用，以確保某個值與子查詢返回的每一個值進(jìn)行比較。
ALL 的語法：
expression comparison_operator ALL (subquery)
- expression：要比較的值或表達(dá)式。
- comparison_operator：比較運算符，比如 >, <, >=, <=, = 等。
- ALL：表示與子查詢返回的每一個值進(jìn)行比較。
- subquery：返回一組值的子查詢。
ALL 的工作原理：
- 如果所有的比較都返回 TRUE，則最終返回 TRUE。
- 如果任意一個比較返回 FALSE，則最終返回 FALSE。
常見用法示例
- 查找大于子查詢返回的所有值
假設(shè)有一個名為 employees 的表，你想查找工資比所有部門平均工資都高的員工。你可以使用 ALL 結(jié)合子查詢來完成。
SELECT employee_name, salary
FROM employees
WHERE salary > ALL (SELECT AVG(salary)FROM employeesGROUP BY department_id
);
- 子查詢返回每個部門的平均工資。
- ALL 確保 salary 比所有部門的平均工資都高。
- 查找小于子查詢返回的所有值
假設(shè)你有一個產(chǎn)品表 products，你想找到價格比所有同類產(chǎn)品的最高價格都低的產(chǎn)品。
SELECT product_name, price
FROM products
WHERE price < ALL (SELECT MAX(price)FROM productsGROUP BY category_id
);
- 子查詢返回每個類別中最高的價格。
- ALL 確保 price 小于所有類別中的最高價格。
- 等于所有子查詢返回的值
你可以用 ALL 來確保一個值與子查詢返回的每一個值相等（這通常不常用，更多情況下使用 IN）。
SELECT employee_name
FROM employees
WHERE department_id = ALL (SELECT department_idFROM departmentsWHERE location = 'New York'
);
這將返回在每個 New York 地點的部門工作的員工，但這種情況相對較少使用，因為一般會用 IN 或者其他方式來完成。
1082.銷售分析I
產(chǎn)品表：Product
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| product_id | int |
| product_name | varchar |
| unit_price | int |
+--------------+---------+
product_id 是這個表的主鍵(具有唯一值的列)。該表的每一行顯示每個產(chǎn)品的名稱和價格。銷售表：Sales
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| seller_id | int |
| product_id | int |
| buyer_id | int |
| sale_date | date |
| quantity | int |
| price | int |
+------ ------+---------+
這個表它可以有重復(fù)的行。 product_id 是 Product 表的外鍵(reference 列)。該表的每一行包含關(guān)于一個銷售的一些信息。
編寫解決方案，找出總銷售額最高的銷售者，如果有并列的，就都展示出來。以 任意順序 返回結(jié)果表。返回結(jié)果格式如下所示。
示例 1:輸入：Product 表：
+------------+--------------+------------+
| product_id | product_name | unit_price |
+------------+--------------+------------+
| 1 | S8 | 1000 |
| 2 | G4 | 800 |
| 3 | iPhone | 1400 |
+------------+--------------+------------+
Sales 表：
+-----------+------------+----------+------------+----------+-------+
| seller_id | product_id | buyer_id | sale_date | quantity | price |
+-----------+------------+----------+------------+----------+-------+
| 1 | 1 | 1 | 2019-01-21 | 2 | 2000 |
| 1 | 2 | 2 | 2019-02-17 | 1 | 800 |
| 2 | 2 | 3 | 2019-06-02 | 1 | 800 |
| 3 | 3 | 4 | 2019-05-13 | 2 | 2800 |
+-----------+------------+----------+------------+----------+-------+
輸出：
+-------------+
| seller_id |
+-------------+
| 1 |
| 3 |
+-------------+
解釋：Id 為 1 和 3 的銷售者，銷售總金額都為最高的 2800。
解答如下：
with t as (select seller_id,sum(price) as sum_prices from Sales GROUP BY seller_id ORDER BY sum_prices desc)select seller_id from t where sum_prices = (select max(sum_prices) from t);
逐步建表，進(jìn)行分解，得出答案。
另一種寫法，后期題目中如果考慮最低或者最高關(guān)鍵詞出現(xiàn)的時候，可以使用ALL
select seller_id from Sales GROUP BY seller_id having sum(price) >= ALL(select sum(price) from Sales GROUP BY seller_id);
1083.銷售分析II
表：Product
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| product_id | int |
| product_name | varchar |
| unit_price | int |
+--------------+---------+
Product_id 是該表的主鍵(具有唯一值的列)。該表的每一行表示每種產(chǎn)品的名稱和價格。表：Sales
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| seller_id | int |
| product_id | int |
| buyer_id | int |
| sale_date | date |
| quantity | int |
| price | int |
+------ ------+---------+
這個表可能有重復(fù)的行。product_id 是 Product 表的外鍵(reference 列)。buyer_id 永遠(yuǎn)不會是 NULL。sale_date 永遠(yuǎn)不會是 NULL。該表的每一行都包含一次銷售的一些信息。
編寫一個解決方案，報告那些買了 S8 而沒有買 iPhone 的 買家。注意，S8 和 iPhone 是 Product 表中顯示的產(chǎn)品。以 任意順序 返回結(jié)果表。
結(jié)果格式如下所示。
示例 1：輸入：Product table:
+------------+--------------+------------+
| product_id | product_name | unit_price |
+------------+--------------+------------+
| 1 | S8 | 1000 |
| 2 | G4 | 800 |
| 3 | iPhone | 1400 |
+------------+--------------+------------+
Sales table:
+-----------+------------+----------+------------+----------+-------+
| seller_id | product_id | buyer_id | sale_date | quantity | price |
+-----------+------------+----------+------------+----------+-------+
| 1 | 1 | 1 | 2019-01-21 | 2 | 2000 |
| 1 | 2 | 2 | 2019-02-17 | 1 | 800 |
| 2 | 1 | 3 | 2019-06-02 | 1 | 800 |
| 3 | 3 | 3 | 2019-05-13 | 2 | 2800 |
+-----------+------------+----------+------------+----------+-------+
輸出：
+-------------+
| buyer_id |
+-------------+
| 1 |
+-------------+
解釋：
id 為 1 的買家購買了一部 S8，但是卻沒有購買 iPhone，而 id 為 3 的買家卻同時購買了這 2 部手機。
解答如下：
with t as (select s.buyer_id,p.product_name from product p LEFT JOIN Sales s on p.product_id = s.product_id where p.product_name = "S8"),t2 as (select s.buyer_id,p.product_name from product p LEFT JOIN Sales s on p.product_id = s.product_id where p.product_name = "Iphone")select distinct t.buyer_id from t LEFT JOIN t2 on t.buyer_id = t2.buyer_id where t2.buyer_id is null;
很巧妙的做法
select s.buyer_idfrom product p, sales swhere p.product_id = s.product_idgroup by s.buyer_idhaving sum(p.product_name='S8') > 0 and sum(p.product_name='iphone') < 1;
1084.銷售分析III
表： Product
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| product_id | int |
| product_name | varchar |
| unit_price | int |
+--------------+---------+
product_id 是該表的主鍵（具有唯一值的列）。
該表的每一行顯示每個產(chǎn)品的名稱和價格。
表：Sales
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| seller_id | int |
| product_id | int |
| buyer_id | int |
| sale_date | date |
| quantity | int |
| price | int |
+------ ------+---------+
這個表可能有重復(fù)的行。product_id 是 Product 表的外鍵（reference 列）。該表的每一行包含關(guān)于一個銷售的一些信息。
編寫解決方案，報告 2019年春季 才售出的產(chǎn)品。即 僅 在 2019-01-01 （含）至 2019-03-31 （含）之間出售的商品。以 任意順序 返回結(jié)果表。結(jié)果格式如下所示。
示例 1:輸入：Product table:
+------------+--------------+------------+
| product_id | product_name | unit_price |
+------------+--------------+------------+
| 1 | S8 | 1000 |
| 2 | G4 | 800 |
| 3 | iPhone | 1400 |
+------------+--------------+------------+
Sales table:
+-----------+------------+----------+------------+----------+-------+
| seller_id | product_id | buyer_id | sale_date | quantity | price |
+-----------+------------+----------+------------+----------+-------+
| 1 | 1 | 1 | 2019-01-21 | 2 | 2000 |
| 1 | 2 | 2 | 2019-02-17 | 1 | 800 |
| 2 | 2 | 3 | 2019-06-02 | 1 | 800 |
| 3 | 3 | 4 | 2019-05-13 | 2 | 2800 |
+-----------+------------+----------+------------+----------+-------+
輸出：
+-------------+--------------+
| product_id | product_name |
+-------------+--------------+
| 1 | S8 |
+-------------+--------------+
解釋:
id 為 1 的產(chǎn)品僅在 2019 年春季銷售。
id 為 2 的產(chǎn)品在 2019 年春季銷售，但也在 2019 年春季之后銷售。
id 為 3 的產(chǎn)品在 2019 年春季之后銷售。
我們只返回 id 為 1 的產(chǎn)品，因為它是 2019 年春季才銷售的產(chǎn)品。
解答如下：
select p.product_id as product_id,product_name from product p join sales son p.product_id=s.product_idgroup by product_id,product_namehaving max(s.sale_date )<='2019-03-31' and min(s.sale_date) >='2019-01-01';