목록 code review/sql (31)
KEEP GOING
[MySQL] LeetCode : Trips and Users (NOT IN)
https://leetcode.com/problems/trips-and-users/ Trips and Users - LeetCode Level up your coding skills and quickly land a job. This is the best place to expand your knowledge and get prepared for your next interview. leetcode.com [서브쿼리 사용] SELECT request_at Day ,ROUND(COUNT(IF(status != 'completed', TRUE, NULL))/COUNT(*), 2)'Cancellation Rate' FROM Trips WHERE client_id NOT IN (SELECT users_id FR..
[MySQL] LeetCode : Tree Node (NOT IN)
https://leetcode.com/problems/tree-node/submissions/ CASE WHEN 사용 ( IN ) SELECT id ,(CASE WHEN p_id IS NULL THEN 'Root' WHEN id IN (SELECT p_id FROM Tree) THEN 'Inner' ELSE 'Leaf' END)AS type FROM Tree ORDER BY id CASE WHEN 사용 ( NOT IN ) SELECT id ,(CASE WHEN p_id IS NULL THEN 'Root' WHEN id NOT IN (SELECT p_id FROM Tree WHERE p_id IS NOT NULL) THEN 'Leaf' ELSE 'Inner' END)AS type FROM Tree ORDE..
[MySQL] LeetCode : Market Analysis
https://leetcode.com/problems/market-analysis-i/ [LEFT JOIN 사용] SELECT Users.user_id as 'buyer_id' ,Users.join_date ,IFNULL(COUNT(order_date), 0) AS 'orders_in_2019' FROM Users LEFT JOIN Orders ON Orders.buyer_id = Users.user_id AND YEAR(Orders.order_date) = '2019' GROUP BY Users.user_id [CTE 사용] WITH O AS( SELECT buyer_id, COUNT(*) AS cnt FROM Orders WHERE YEAR(order_date) = '2019' GROUP BY buy..
[MySQL] 3주차 (for coding test)
https://leetcode.com/problems/trips-and-users/ Trips and Users - LeetCode Level up your coding skills and quickly land a job. This is the best place to expand your knowledge and get prepared for your next interview. leetcode.com a https://leetcode.com/problems/second-highest-salary/ Second Highest Salary - LeetCode Level up your coding skills and quickly land a job. This is the best place to exp..
[MySQL] 2주차
https://leetcode.com/problems/reformat-department-table/ Reformat Department Table - LeetCode Level up your coding skills and quickly land a job. This is the best place to expand your knowledge and get prepared for your next interview. leetcode.com [if(조건, 참, 거짓), CASE WHEN 사용] SELECT id ,SUM(CASE WHEN month = 'Jan' THEN revenue ELSE null END) as 'Jan_Revenue' ,SUM(IF(month='Feb', revenue, null)..
[순위 구하기] https://leetcode.com/problems/rank-scores/submissions/ Rank Scores - LeetCode Level up your coding skills and quickly land a job. This is the best place to expand your knowledge and get prepared for your next interview. leetcode.com 1. rank : 동일한 값은 동일한 순위 부여 * 직업별로 순위를 매기고 싶은 경우) RANK() OVER (PARTITION BY JOB ORDER BY SAL DESC) SELECT score ,RANK() OVER(ORDER BY score DESC)as 'rank' FR..
[MySQL] LeetCode : Nth highest Salary
https://leetcode.com/problems/nth-highest-salary/ Nth Highest Salary - LeetCode Level up your coding skills and quickly land a job. This is the best place to expand your knowledge and get prepared for your next interview. leetcode.com 1. case when 사용 CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT BEGIN RETURN ( # Write your MySQL query statement below. SELECT CASE WHEN COUNT(tmp.salary) ..
Regular Expression(정규 표현식) 이란? - 정규 표현식은 특정한 규칙을 가진 문자열의 집합을 표현하는데 쓰이는 형식 언어 - 문자열을 처리하는 방법 중의 하나 특정한 조건의 문자를 ‘검색’하거나 ‘치환’할 때 사용 - mySQL에서는 REGEXP라는 키워드를 사용하여 정규표현식을 처리함 [기본 형식] SELECT 컬럼명 FROM 테이블명 WHERE 컬럼명 REGEXP '정규표현식'; [기본 문법] ^ : 시작 * : 끝 [abc] : a, b, c 중 하나 ^[abc] : a, b, c 중 하나로 시작하는 문자 ^[abc].* : a, b, c 중 하나로 시작하는 문자열 [^abc] : a, b, c 전부 아님 \d : 하나의 숫자 . : 문자 .* : 문자의 반복 즉 문자열 [a-z] ..
