# 基本功 ## [1757. Recyclable and Low Fat Products](https://leetcode.com/problems/recyclable-and-low-fat-products/?envType=study-plan-v2&envId=top-sql-50) :::success 编写一个解决方案，找出既低脂又可回收的产品 ID。 按任意顺序返回结果表。 解題思路 就是篩選出 low_fats 和 recyclable 為Y的結果 ::: :::spoiler code ``` select product_id from Products where low_fats = 'Y' && recyclable ='Y' ``` ::: ## [584. Find Customer Referee](https://leetcode.com/problems/find-customer-referee/?envType=study-plan-v2&envId=top-sql-50) :::success 題目要求: 查找不是由 id = 2 的客户推荐的客户姓名。 按任意顺序返回结果表。 解題思路 篩選出 referee_id 不是2的結果 >**在使用 != 或 <> 的時會發現排除掉所有 NULL** **這是因為 NULL代表缺失值 且 NULL又不等於NULL 也不等於 任何值** **無法進行比較 因此 NULL 會直接被排除掉** ::: :::spoiler code ``` select name from Customer where referee_id <> 2 or referee_id is NULL ``` ::: ## [595. Big Countries](https://leetcode.com/problems/big-countries/?envType=study-plan-v2&envId=top-sql-50) :::success 一个国家大的条件是 面积至少有 300 万（即 3000000 平方公里），或 人口至少有两千五百万（即 25000000）。 请写出求大国的名称、人口和面积的解法。 按任意顺序返回结果表。 解題思路 篩選出面積至少300萬 或是人口 至少兩千五百萬 ::: :::spoiler code ``` select name, population, area from World where area >= 3000000 || population >= 25000000 ``` ::: ## [1148. Article Views I](https://leetcode.com/problems/article-views-i/?envType=study-plan-v2&envId=top-sql-50) :::success 该表没有主键（具有唯一值的列），因此可能有重复行。 该表中的每一行都表示某个查看者在某个日期查看了某篇文章（由某个作者撰写）。 请注意，相同的 author_id 和 viewer_id 表示同一个人。 编写一个解决方案，找出所有至少浏览过自己一篇文章的作者。 返回按 id 升序排序的结果表。 解題思路 篩選出 author_id = viewer_id 也就是 看過自己文章的作者 再按照id排列 考慮到重複值的部分 所以 印出需要加上 distinct 印出不重複的結果 ::: :::spoiler code ``` select DISTINCT author_id as id from Views where author_id = viewer_id order by id ``` ::: ## [1683. Invalid Tweets](https://leetcode.com/problems/invalid-tweets/description/?envType=study-plan-v2&envId=top-sql-50) :::success 编写一个解决方案，找出无效推文的 ID。如果推文内容中使用的字符数严格大于 15，则该推文无效。 按任意顺序返回结果表。 解題思路 篩選出content欄位的字數大於15的結果 >LENGTH()、LEN() 函數是用來取得字串長度。 ::: :::spoiler code ``` select tweet_id from Tweets where LENGTH(content) > 15 ``` ::: # Base Join ## [1378. Replace Employee ID With The Unique Identifier](https://leetcode.com/problems/replace-employee-id-with-the-unique-identifier/?envType=study-plan-v2&envId=top-sql-50) :::success 编写一个解决方案，显示每个用户的唯一 ID，如果用户没有唯一 ID，则替换为显示空值。 以任意顺序返回结果表。 解題思路 Employees表 和 EmployeeUNI表 的關聯性是建立在Id欄位上 但是必須考慮到有些用戶沒有唯一ID的情況 所以使用Employees作為主表確保列出所有員工 即使沒有匹配的唯一ID 仍會印出 只是被轉換成NULL ::: :::spoiler code ``` select u.unique_id , e.name from Employees as e left join EmployeeUNI as u on e.id = u.id ``` ::: ## [1068. Product Sales Analysis I](https://leetcode.com/problems/product-sales-analysis-i/?envType=study-plan-v2&envId=top-sql-50) :::success 编写一个解决方案，以报告销售表中每个 sale_id 的 product_name、年份和价格。 按任意顺序返回结果表。 解題思路 兩個表的關聯建立在 product_id欄位上 ::: :::spoiler code ``` SELECT p.product_name, s.year, s.price from Sales as s join Product as p on s.product_id = p.product_id ``` ::: ## [1581. Customer Who Visited but Did Not Make Any Transactions](https://leetcode.com/problems/customer-who-visited-but-did-not-make-any-transactions/?envType=study-plan-v2&envId=top-sql-50) :::success 编写一个解决方案，以查找未进行任何交易的访问用户的 ID 及其进行此类访问的次数。 返回按任意顺序排列的结果表。 解題思路 因為要找出沒有進行任何交易的訪問用戶 兩個表可以看出關聯性的基礎建立在visit_id上 然後以Visits當作主表使用 確定列出所有訪問用戶 如此沒有進行過交易用戶的用戶將會呈現null 然後以用戶進行分組進而計算訪問次數 ::: :::spoiler code ``` #因為要找出沒有進行任何交易的訪問ID 所以要確表印出所有訪問資訊 也就是使用 left join select v.customer_id, count(v.visit_id) as count_no_trans from Visits as v left join Transactions as t on v.visit_id =t.visit_id where t.transaction_id is null group by v.customer_id ``` ::: ## [197. Rising Temperature](https://leetcode.com/problems/rising-temperature/?envType=study-plan-v2&envId=top-sql-50) :::success 请编写一个解决方案，以查找与前一天（昨天）相比温度较高的所有日期的 Id。 以任意顺序返回结果表。 解題思路 我們需要以自身合併, 但不是單純自身合併 而是與前一天的自己做合併 所以關聯性是建立在 當天=前一天(當天與前一天相匹配)的情況下 然後再透過篩選條件找出溫度比前天高的所有結果 ::: :::spoiler code ``` select w1.id from Weather as w1 join Weather as w2 on DATE_ADD(w1.recordDate,INTERVAL -1 DAY) = w2.recordDate where w1.temperature > w2.temperature ``` ::: ## [1661. Average Time of Process per Machine](https://leetcode.com/problems/average-time-of-process-per-machine/?envType=study-plan-v2&envId=top-sql-50) :::success 有一个工厂网站，网站上有多台机器，每台机器都运行相同数量的进程。请编写一个解决方案，找出每台机器完成一个进程所需的平均时间。 完成一个进程的时间是 "结束 "时间戳减去 "开始 "时间戳。平均时间的计算方法是：机器上完成每个进程的总时间除以运行的进程数。 结果表中应包含机器 ID 和作为 processing_time 的平均时间，并四舍五入到小数点后 3 位。 以任意顺序返回结果表。 解題思路 很明顯的 我需要兩個欄位 一個叫做start 一個叫做end,相減後取得啟動時間, 所以必須把關聯建立在同個machine_id 以及 同個 process_id 再去篩選開始和結束 再依照machine_id 分組 計算平均時間 ::: :::spoiler code ``` select a1.machine_id , ROUND(AVG(a2.timestamp - a1.timestamp),3) as processing_time from Activity as a1 join Activity as a2 on a1.machine_id = a2.machine_id AND a1.process_id = a2.process_id where a1.activity_type = 'start' and a2.activity_type = 'end' group by a1.machine_id ``` ## 效率更高 CASE WHEN ``` SELECT machine_id, ROUND(AVG(end_time - start_time), 3) AS processing_time FROM ( SELECT process_id, machine_id, MAX(CASE WHEN activity_type = 'start' THEN timestamp END) AS start_time, MAX(CASE WHEN activity_type = 'end' THEN timestamp END) AS end_time FROM Activity GROUP BY process_id, machine_id ) t GROUP BY machine_id; ``` ::: ## [577. Employee Bonus](https://leetcode.com/problems/employee-bonus/?envType=study-plan-v2&envId=top-sql-50) :::success 编写一个解决方案，报告奖金少于 1000 的每位员工的姓名和奖金数额。 以任意顺序返回结果表。' 解題思路 Employee當作主表 確保每位員工都有列出 然後 篩選出獎金少於1000的員工 或是 獎金為 null的員工 > 因為null不等於任何值 所以無法進行比較 SQL會直接排除掉 ::: :::spoiler code ``` select e.name, b.bonus from Employee as e left join Bonus as b on e.empId = b.empId where b.bonus < 1000 || b.bonus is NULL ``` ::: ## [1280. Students and Examinations](https://leetcode.com/problems/students-and-examinations/description/?envType=study-plan-v2&envId=top-sql-50) :::success 返回按 student_id 和 subject_name 排序的结果表。 解題絲路 成绩表应包含所有学生和所有科目。 首先看到學生和科目之間沒有關聯性 但每個學生必須和科目進行組合 這邊就直接使用笛卡爾乘積 得到一個新表具有學生對應所有科目的資料 a-數學, a-物理, a-程式 , b-數學...... 然後以學生和科目當作主表 匹配每個學生和每個科目的考試記錄,確保沒有參加考試仍會顯示 ::: :::spoiler code ``` select s.student_id,s.student_name,subj.subject_name, count(e.subject_name) as attended_exams from Students as s CROSS JOIN Subjects as subj left join Examinations as e on s.student_id = e.student_id and subj.subject_name = e.subject_name group by s.student_name, subj.subject_name order by s.student_id ,subj.subject_name ``` ::: ## [570. Managers with at Least 5 Direct Reports](https://leetcode.com/problems/managers-with-at-least-5-direct-reports/description/?envType=study-plan-v2&envId=top-sql-50) :::success 與自身當主表 id與 managerId進行關聯 然後按照e1.group ::: :::spoiler code ``` SELECT case when e1.name is null then null else e1.name end as name FROM Employee as e1 LEFT JOIN Employee as e2 ON e1.id = e2.managerId --列出每位員工的下屬 group by e1.id having COUNT(e2.id) > 4 -- 篩選下屬超過五位的經理 ``` ::: ## [1934. Confirmation Rate](https://leetcode.com/problems/confirmation-rate/description/?envType=study-plan-v2&envId=top-sql-50) :::success 用户的确认率是 "确认 "信息的数量除以请求确认信息的总数。没有请求任何确认信息的用户的确认率为 0。 写出求解方案，找出每个用户的确认率。 ::: :::spoiler code ``` select s.user_id,ROUND(IFNULL(SUM(action = 'confirmed ')/COUNT(1),0),2) as confirmation_rate FROM Signups as s left join Confirmations as c on s.user_id = c.user_id group by s.user_id ----- ps 也可以直接使用avg ``` ::: # SQL SERVER 函數 類似 CASE WHER # IIF(條件,True返回值,False返回值) * MySQL可使用IF ![image](https://hackmd.io/_uploads/Bym4MzwZxx.png) # LEN(expression): 回傳字串長度, 不包含尾部空白 ![image](https://hackmd.io/_uploads/SyijmzPbgl.png) # CONCAT(str1, str2):拼接字串 ![image](https://hackmd.io/_uploads/HJ_vLfPZgg.png) # CAST(expression AS target_data_type): 型別轉換 # CONVERT(target_data_type, expression, [style]) * 常用樣式碼對照表（日期） Style 輸出格式 說明 100 Mon dd yyyy hh:miAM (or PM) 美國預設格式 101 mm/dd/yyyy 美式日期格式 102 yyyy.mm.dd ISO 標準格式 103 dd/mm/yyyy 英國日期格式 111 yyyy/mm/dd 日本日期格式 120 yyyy-mm-dd hh:mi:ss ODBC 標準格式 ![image](https://hackmd.io/_uploads/Sy509AjGxx.png) # ISNULL(EXPRESSION,True返回值):為NULL時回傳替代值 ![image](https://hackmd.io/_uploads/rJjGuMwZgx.png) # TRIM(String):去除字串左右空白 ![image](https://hackmd.io/_uploads/BydzL7vbxl.png) # CEILING(numeric_expression):將數值無條件進位到最接近的整數（即「向上取整數」） ![image](https://hackmd.io/_uploads/B1I29QD-ee.png) # FLOOR(numeric_expression):將數值無條件捨去到最接近的整數（即「向下取整數」） ![image](https://hackmd.io/_uploads/ByLWcQwbee.png) # ROW_NUMBER(): 排序函數 產生虛擬主鍵 使用情境:在設定view表時，缺少了流水號 這時可以使用 ROW_NUMBER() OVER (ORDER BY...)可以將查詢結果按照指定欄位排序，從1開始排序 * ROW_NUMBER():代表要產生流水號 * OVER:是SQL的窗口(Window)語法，用來定義計算範圍 * ORDER BY column:根據指定的欄位排序後產生流水號 * ==注意 ROW_NUMBER()是查尋執行時動態產生，不會寫入資料庫== ![image](https://hackmd.io/_uploads/rkK4bX40yx.png) ## 想產出「第幾組」、「第幾個」、「第幾輪」的資料, 保證相同值分在同一 GROUP_NO | 函數名稱 | 排名重複時是否跳號？ | 相同值是否會有相同排名？ | | -------- | -------------------- | -------- | | RANK() | 跳號（1, 2, 2, 4） | 是 | DENSE_RANK()| 不跳號(1,2,2,3) | 是 | | | ROW_NUMBER() | 不跳號，每筆唯一（1, 2, 3, 4） | 否（每筆都不同） | ``` WITH CanCelKeyMinSeq AS ( -- 取同組ACCT_CANCEL_KEY最小SEQ SELECT E.ACCT_CANCEL_KEY, MIN(SEQ) AS MIN_SEQ FROM HOAMS.HOAMS.HOAMS_TB_CUR_ENT E WHERE E.ACCT_CANCEL_KEY LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][A-Z][A-Z][0-9][0-9][0-9][0-9][0-9][0-9]' OR E.ACCT_CANCEL_KEY LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]C[0-9][0-9][0-9][0-9][0-9][0-9]' GROUP BY E.ACCT_CANCEL_KEY) -- 計算 GROUP_NO SELECT TRIM( ISNULL(T.MEMO_NAME, '') + CASE WHEN T.MEMO_NAME IS NOT NULL AND T.MEMO1 IS NOT NULL THEN ' ' ELSE '' END + ISNULL(T.MEMO1, '') ) AS MEMO1, E.SEQ, E.ACCT_CANCEL_KEY, CASE WHEN E.ACCT_CANCEL_KEY LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][A-Z][A-Z][0-9][0-9][0-9][0-9][0-9][0-9]' OR E.ACCT_CANCEL_KEY LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]C[0-9][0-9][0-9][0-9][0-9][0-9]' THEN DENSE_RANK() OVER (ORDER BY KM.MIN_SEQ) ELSE CEILING( ROW_NUMBER() OVER ( PARTITION BY T.TRAN_ID, T.ACCT_CD, T.DC, T.CURR_CD ORDER BY T.TRAN_ID, E.SEQ ) / 5.0 ) END AS GROUP_NO FROM HOAMS.HOAMS.HOAMS_V_RPT_PDF_D0300_DATASOURCE T LEFT JOIN HOAMS.HOAMS_TB_CUR_ENT E ON T.PK = E.ID LEFT JOIN CanCelKeyMinSeq KM ON E.ACCT_CANCEL_KEY = KM.ACCT_CANCEL_KEY WHERE E.TRAN_ID = 1098 order by GROUP_NO ``` ![image](https://hackmd.io/_uploads/S1pms22Blg.png) ![image](https://hackmd.io/_uploads/BJn8i22Slx.png) ![image](https://hackmd.io/_uploads/Syhwsnhrge.png)