SQL13 [MySQL] 데이터 삭제 https://leetcode.com/problems/delete-duplicate-emails/ Delete Duplicate Emails - 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 위 문제를 풀고 DELETE 구문에 대해 정리하고자 함. DELETE FROM 테이블 이름 WHERE 조건 DELETE p1 FROM PERSON p1, PERSON p2 WHERE p1.email = p2.email and p1.id > p2.id ; 위와 같이 뷰가 두 .. 2022. 3. 31. [MySQL] 그룹별 상위 N 개 https://leetcode.com/problems/department-top-three-salaries/ Department Top Three Salaries - 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 위의 문제를 풀다 RANK에서 그룹별로 처리하는 방법을 알게 되어 정리. 결론은 그룹별로 해당 Row의 Rank를 매기고 싶을 때 PARTITION BY를 사용하면 된다. SELECT Department, Employee, salary FROM ( SE.. 2022. 3. 31. [LeetCode] Department Highest Salary https://leetcode.com/problems/department-highest-salary/ Department 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 부서별 봉급이 가장 높은 사람의 아이디, 봉급, 부서이름을 출력 나의 풀이 SELECT D.name AS Department, Employee, salary FROM ( SELECT name AS Employee, salary, E.departmentId FROM E.. 2022. 3. 31. [LeetCode] Customers Who Never Order https://leetcode.com/problems/customers-who-never-order/ Customers Who Never Order - 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 상품을 주문한 적 없는 회원을 찾는 문제 나의 풀이 - Left Join 활용 SELECT Customers FROM (SELECT name AS Customers, customerId FROM Customers AS c LEFT JOIN Orders AS o ON .. 2022. 3. 31. [LeetCode] Duplicate Emails https://leetcode.com/problems/duplicate-emails/ Duplicate Emails - 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 email이 중복된 id를 출력하면 되는 문제 SELECT email AS Email FROM Person GROUP BY email HAVING COUNT(email) > 1 ; GROUP BY + HAVING을 활용하여 간단하게 풀이할 수 있다. 2022. 3. 31. [LeetCode] Employees Earning More Than Their Managers https://leetcode.com/problems/employees-earning-more-than-their-managers/ Employees Earning More Than Their Managers - 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 E.name AS Employee FROM Employee E JOIN Employee M ON E.managerId = M.id WHE.. 2022. 3. 31. [LeetCode] Consecutive Numbers (MySQL) https://leetcode.com/problems/consecutive-numbers/ Consecutive Numbers - 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 row 3개 연속해서 등장하는 num을 찾는 문제. 1. Where IN 활용 select distinct Num as ConsecutiveNums from Logs where (Id+1,Num) in (Select * from Logs) and (ID+2, Num) in (Select .. 2022. 3. 31. [MySQL] rank함수 - 랭킹 https://leetcode.com/problems/rank-scores/ 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 위 문제를 풀면서 알게 된 사실을 정리. MySQL 8 버전 이상부터는 rank, dense_rank 함수 사용 가능 MySQL 버전 확인 SELECT VERSION() rank 함수와, dense_rank 함수 모두 같은 순위는 같은 랭크로 처리됨 rank함수는 순위가 밀려남 EX) 1, 1, 3, 4, 5 den.. 2022. 3. 31. [MySQL] 변수 처리(local variable vs. user variable) 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 위 문제를 풀면서 변수(특히 지역변수 처리에 대해 알게 되어서 정리해 놓고자 함.) CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT BEGIN RETURN ( # Write your MySQL query statement below. ); E.. 2022. 3. 31. [MySQL] N번째로 큰 값 위와 같은 테이블이 있을 때 가장 큰 값을 구하고 싶으면 MAX 함수를 사용하면 된다. 그럼 N번째로 큰 값은 어떻게 불러올까? 여러 방법이 있겠지만, LIMIT OFFSET을 사용하면 된다. 2021.09.17 - [Database/PostgreSQL] - [PostgreSQL] limit, offset [PostgreSQL] limit, offset limit, offset을 알아보자. LIMIT는 몇 개 불러올건지, OFFSET은 어디부터 불러올건지 -- 처음부터 시작해서 100개 Row 불러오기 SELECT * FROM table LIMIT 100; SELECT * FROM table LIMIT 100 OFFSET 0; -- 101.. hkim-data.tistory.com -- 두번째로 큰 값 .. 2022. 3. 31. [MySQL] JOIN 이후 NULL 처리 https://leetcode.com/problems/combine-two-tables/ Combine Two Tables - 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 데이터 생성 CREATE table If Not Exists Person (personId int, firstName varchar(255), lastName varchar(255)); Create table If Not Exists Address (addressId int, personId.. 2022. 3. 31. SQL 쿼리 연습 사이트, 쿼리 정리 사이트 http://sqlfiddle.com/ SQL Fiddle | A tool for easy online testing and sharing of database problems and their solutions. Query Panel Use this panel to try to solve the problem with other SQL statements (SELECTs, etc...). Results will be displayed below. Share your queries by copying and pasting the URL that is generated after each run. sqlfiddle.com 위 사이트에서 데이터베이스 만들고 간단하게 테스트해볼 수 있어서 편하다. https:.. 2022. 3. 31. [LeetCode] Combine Two Tables (MySQL) https://leetcode.com/problems/combine-two-tables/ Combine Two Tables - 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 personId로 조인하는 문제 outer join을 하면 된다. SELECT firstName, lastName, city, state FROM Person p LEFT JOIN Address a ON p.personId = a.personId ; 2022. 3. 31. 이전 1 다음
