zimslog

Leetcode 180, 511 - 윈도우 함수 180. Consecutive Numbers# Write your MySQL query statement below-- 3번 이상 연속으로 나타나는 모든 숫자-- 정렬 없음-- ID로 정렬한 다음, 2번, 1번 앞의 숫자 현재 숫자와 비교SELECT DISTINCT T.num as ConsecutiveNumsFROM (SELECT LAG(num, 2) OVER (ORDER BY id) AS lag2, -- 2행 앞 num LAG(num, 1) OVER (ORDER BY id) AS lag1, -- 1행 앞 num num -- 현재 num FROM Logs) TWHERE T.lag1 = T.num AND T.lag2 = T.num 5..

1. Department Top Three Salaries //각 부서마다 연봉 top3 SELECT D.Name as Department, E.Name as Employee, E.Salary as Salary FROM Department D JOIN ( SELECT DepartmentId, Name, Salary, RANK() OVER (PARTITION BY DepartmentId ORDER BY Salary DESC) as rank FROM Employee WHERE rank = "2013-10-01" AND Request_at =100 and b.salary >= 100 and c.salary > =100 order by visit_date 4. Game Play Analysis 5 //해당 날짜..

11. Activity Participant SELECT activity FROM Friends GROUP BY activity HAVING COUNT(*) != (SELECT COUNT(*) FROM Friends GROUP BY activity ORDER BY COUNT(*) ASC LIMIT 1) AND COUNT(*) != (SELECT COUNT(*) FROM Friends GROUP BY activity ORDER BY COUNT(*) DESC LIMIT 1) 12. Number Of Trusted Contacts of a Customer SELECT invoice_id, customer_name, price, COUNT(contact_email) as contacts_cnt, sum(IF(c..

1. Nth Hghtest Salary (FUNCTION) CREATE FUNCTION getNth (N INT) RETURNS INT BEGIN RETURN ( SELECT Salary From Employee LIMIT 1, N-1 //LIMIT 1 OFFSET N-1 ); END CREATE FUNCTION getNth (N INT) RETURNS INT BEGIN SET N = N-1 RETURN ( SELECT Salary From Employee LIMIT 1, N //LIMIT 1 OFFSET N ); END 2. Rank Scores (DENSE_RANK, -보다 같거나 큰수를 세서 RANK) //DENSE_RANK() OVER () SELECT score, DENSE_RANK() OVER..

21. Recyclable and Low Fat Products SELECT product_id FROM Products WHERE SUM(low_fats, recyclable) = 2 low_fats = TRUE AND recyclable = TRUE 22. Warehouse Manger SELECT W.name, SUM(Width*Lenght*Height*unit) as volume FROM Warehouse W JOIN Products P ON W.product_id = P.product_id GROUP BY W.name 23. Average Time of Process per Machine //기계별 프로세스 처리량 구하고 그걸 다 더하고 SELECT machine_id, avg(process_t..

11. Classes More Than 5 Students (COUNT DISTINCT)SELECT classFROM CoursesGROUP BY classHAVING COUNT(**Distinct**(student)) >= 512. Not Boring Movies//boring이 아닌 odd넘버 정렬은 rating descSELECT id, movie, description, ratingFROM cinemaWHERE description != 'boring' AND id%2 = 1ORDER BY rating desc13. Swap Salary (UPDATE)UPDATE salary SET sex = IF(sex= 'm', 'f', 'm')UPDATE salary SET sex = CASE WHEN se..

1. Combine Two Table (LEFT JOIN) Person, Address 두 테이블 SELECT A.FirstName, A.LastName, B.City, B.State FROM Person A LEFT JOIN Address B ON A.PersonId = B.PersonId // 그냥 JOIN = INNER JOIN 2. Second Hightest Salary (LIMIT, OFFSET) SELECT E.Salary FROM Employee E ORDER BY E.Salary desc LIMIT 1 OFFSET 1 //LIMIT A 출력할 행의 수 //OFFSET B 몇 번째 ROW부터 출력할지 //=LIMIT B, A //SQL에서 첫번쨰 ROW = 0 //USING SUBQUERY..