---
# System prepended metadata

title: SQL隨筆
tags: [SQL]

---



# 基本功



## [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)
