โ๏ธ Programmers MySQL ์ ๋ต๋ฅ 69% ์ ์ฒด ํ์ด
1๏ธโฃ ์ฌ๊ตฌ๋งค๊ฐ ์ผ์ด๋ ์ํ๊ณผ ํ์ ๋ฆฌ์คํธ ๊ตฌํ๊ธฐ
SELECT USER_ID,PRODUCT_ID
FROM ONLINE_SALE
GROUP BY USER_ID, PRODUCT_ID # USER_ID ๊ธฐ์ค ํ๋ฒ, PRODUCT_ID ๊ธฐ์ค ํ๋ฒ ๊ทธ๋ฃนํ
HAVING COUNT(online_sale_id) > 1
ORDER BY USER_ID ASC, PRODUCT_ID DESC;

2๏ธโฃ ํค๋น ์ ์ ๊ฐ ์์ ํ ์ฅ์ (https://school.programmers.co.kr/learn/courses/30/lessons/77487)
# ๋ฐฉ๋ฒ (1) : left join ์ฌ์ฉ
SELECT P.ID, P.NAME, P.HOST_ID
FROM PLACES AS P
LEFT JOIN ( # SUB ํ
์ด๋ธ์ ํตํด ๋ฑ๋กํ ์ฅ์๊ฐ 2๊ฐ ์ด์์ธ ์ ์ ๋ชฉ๋ก ํ์ธ
SELECT HOST_ID
FROM PLACES
GROUP BY HOST_ID
HAVING COUNT(ID) >1
) AS SP
ON P.HOST_ID = SP.HOST_ID # LEFT join ํตํด ์ ๋ณ
WHERE SP.HOST_ID IS NOT NULL
ORDER BY P.ID;
# ๋ฐฉ๋ฒ (2)-1 : inner join ์ฌ์ฉ
SELECT P.ID, P.NAME, P.HOST_ID
FROM PLACES AS P
INNER JOIN (
SELECT HOST_ID
FROM PLACES
GROUP BY HOST_ID
HAVING COUNT(ID) >1
) AS SP
ON P.HOST_ID = SP.HOST_ID
ORDER BY P.ID;
# ๋ฐฉ๋ฒ (2)-2 : inner join ์ ์ข ๋ ๊น๋ํ๊ฒ
SELECT P.ID, P.NAME, P.HOST_ID
FROM PLACES AS P
JOIN (
SELECT HOST_ID, COUNT(ID) AS 'COUNT'
FROM PLACES
GROUP BY HOST_ID
) AS SP
ON P.HOST_ID = SP.HOST_ID
WHERE SP.COUNT > 1
ORDER BY P.ID;
# ๋ฐฉ๋ฒ (3) : IN ์ฌ์ฉ
SELECT *
FROM PLACES
WHERE HOST_ID IN (
SELECT HOST_ID
FROM PLACES
GROUP BY HOST_ID
HAVING COUNT(ID) > 1
)
ORDER BY ID;
3๏ธโฃ ๊ฐ๊ฒฉ๋ ๋ณ ์ํ ๊ฐ์ ๊ตฌํ๊ธฐ (https://school.programmers.co.kr/learn/courses/30/lessons/131530)
SELECT (
CASE
WHEN PRICE < 10000 THEN 0
ELSE TRUNCATE(PRICE, -4) # ๋ด๋ฆผ์ ํตํด ๋ฒ์ ์ง์
END
) AS PRICE_GROUP , COUNT(PRODUCT_ID)
FROM PRODUCT
GROUP BY PRICE_GROUP
ORDER BY PRICE_GROUP;
# ๋ฐ์ฌ๋ฆผ(ROUND) ๊ณผ ๋ด๋ฆผ(TRUNCATE)
ROUND([์ซ์], [๋ฐ์ฌ๋ฆผํ ์๋ฆฟ์]) # ์ซ์๋ฅผ ๋ฐ์ฌ๋ฆผํ ์๋ฆฟ์+1 ์๋ฆฟ์์์ ๋ฐ์ฌ๋ฆผํจ, ์๋ฆฟ์ ๋ช
์๋ ์ ํ
# ์์
SELECT ROUND(3456.1234567) FROM table_name # (๊ฒฐ๊ณผ) : 3456
SELECT ROUND(3456.1234567 ,-1) FROM table_name # (๊ฒฐ๊ณผ) : 3460
TRUNCATE([์ซ์], [๋ฒ๋ฆด ์๋ฆฟ์]) # ์ซ์๋ฅผ ๋ฒ๋ฆด ์๋ฆฟ์ ์๋๋ก ๋ฒ๋ฆผ, ์๋ฆฟ์ ๋ช
์๋ ํ์
#์์
SELECT TRUNCATE(3456.1234567 ,1) FROM table_name; # (๊ฒฐ๊ณผ) : 3456.1
SELECT TRUNCATE(3456.1234567 ,-2) FROM table_name; # (๊ฒฐ๊ณผ) : 3400
'SQL ๐ฌ' ์นดํ ๊ณ ๋ฆฌ์ ๋ค๋ฅธ ๊ธ
| (Programmers) SQL ํ์ด (10) (0) | 2022.10.31 |
|---|---|
| (Programmers) SQL ํ์ด (9) (0) | 2022.10.27 |
| (Programmers) SQL ํ์ด (7) (1) | 2022.10.25 |
| (Programmers) SQL ํ์ด (6) (0) | 2022.10.24 |
| (Programmers) SQL ํ์ด (5) (0) | 2022.10.23 |