---
tags: sql, LeetCode
disqus: HackMD
---

# 1327. List the Products Ordered in a Period

`Rearrange Products Table`

透過`leetcode 1327`[List the Products Ordered in a Period](https://leetcode.com/problems/list-the-products-ordered-in-a-period/description/?envType=study-plan-v2&id=top-sql-50)來練習

## 題目說明:
撰寫一個 SQL 查詢，以獲取在 2020 年 2 月至少訂購了一定數量`100`的產品名稱和其金額。
返回的結果表可以按任意順序排列。
其中，product_name 是產品名稱，total_amount 是在 2020 年 2 月至少訂購了一定數量的該產品的總金額。



## 解題:
第一種

select a.* from (
  select m.product_name,sum(m.unit) as unit from (
    select p.product_id,p.product_name,substring(CAST(o.order_date AS varchar(10)),6,2) as o_date,o.unit 
    from Products p
    left outer join Orders o on o.product_id = p.product_id
    where substring(CAST(o.order_date AS varchar(10)),6,2) = '02'
  ) m
  group by m.product_name 
) a
where a.unit >= 100

第二種

SELECT product_name, SUM(unit) AS unit
FROM Products P
JOIN Orders O
ON P.product_id = O.product_id
WHERE MONTH(order_date) = 2 
GROUP BY product_name
HAVING SUM(unit) >= 100