[LeetCode/MSSQL] 1327. List the Products Ordered in a Period

 

문제링크 

 

1327. List the Products Ordered in a Period

 

https://leetcode.com/problems/list-the-products-ordered-in-a-period/description/?envType=study-plan-v2&envId=top-sql-50

LeetCode - The World's Leading Online Programming Learning Platform

 

풀이과정

 

생각 1. 2020 년 2월의 unit 을 다 합해야하니까 

orders table 에 group by product_id 를 해서 sum(unit) , where order_date between 2020-02-01 and 2020-02-31  and unit >= 100 한다. 

그 다음 Products 와 join. product-name 이랑 order_date 이랑  unit 나오게 한다. 

 

기억하면 좋을 것 

date between 을 해줄때 convert 해줘야한다 ~!!

convert(date , '20200231') 하면 2월 31일은 없는 날짜이기 때문에 에러남.

convert(date , '2020-01-01') 해줘도 정상적으로 변환된다. 

해결 코드

/* Write your T-SQL query statement below */
SELECT a.product_name , b.unit
FROM Products AS a
JOIN 
(
    SELECT product_id , SUM(unit) AS unit
    FROM Orders
    WHERE order_date BETWEEN CONVERT(DATE ,'20200201') AND CONVERT(DATE, '20200229')
    GROUP BY product_id 
    HAVING SUM(unit) >= 100
) AS b
ON a.product_id = b.product_id

 

 

 

Submission Detail