본문 바로가기

다중컬럼 서브쿼리4

[leetcode / MySQL] 1070. Product Sales Analysis III 문제 💡 Write a solution to select the product id, year, quantity, and price for the first year of every product sold. Return the resulting table in any order. 테이블 형태 풀이 SELECT product_id, year AS first_year, quantity, price FROM Sales WHERE (product_id, year) IN (SELECT product_id, MIN(year) AS year FROM Sales GROUP BY product_id) 해설 SELECT product_id, year AS first_year, quantity, price FROM Sale.. 2024. 2. 27.
[leetcode / MySQL] 585. Investments in 2016 문제 💡 Write a solution to report the sum of all total investment values in 2016 tiv_2016, for all policyholders who: have the same tiv_2015 value as one or more other policyholders, and are not located in the same city as any other policyholder (i.e., the (lat, lon) attribute pairs must be unique). Round tiv_2016 to two decimal places. 테이블 형태 풀이 SELECT ROUND(SUM(tiv_2016),2) AS tiv_2016 FROM Insu.. 2024. 2. 22.
[leetcode / MySQL] 184. Department Highest Salary 문제 💡 Write a solution to find employees who have the highest salary in each of the departments. Return the result table in any order. 테이블 형태 풀이 SELECT D.name AS Department , E.name AS Employee , salary AS Salary FROM Employee E INNER JOIN Department D ON E.departmentId = D.id WHERE (departmentId, salary) IN (SELECT departmentId, MAX(salary) FROM Employee GROUP BY departmentId) 해설 FROM Employee E.. 2024. 1. 30.
[프로그래머스 / MySQL] 즐겨찾기가 가장 많은 식당 정보 출력하기 문제 💡 REST_INFO 테이블에서 음식종류별로 즐겨찾기수가 가장 많은 식당의 음식 종류, ID, 식당 이름, 즐겨찾기수를 조회하는 SQL문을 작성해주세요. 이때 결과는 음식 종류를 기준으로 내림차순 정렬해주세요. 테이블 형태 풀이 SELECT FOOD_TYPE, REST_ID, REST_NAME, FAVORITES FROM REST_INFO WHERE (FOOD_TYPE, FAVORITES) IN (SELECT FOOD_TYPE, MAX(FAVORITES) FROM REST_INFO GROUP BY FOOD_TYPE) ORDER BY FOOD_TYPE DESC 해설 SELECT FOOD_TYPE, REST_ID, REST_NAME, FAVORITES FROM REST_INFO 결과 예시와 일치하도록 .. 2024. 1. 29.

티스토리툴바