1. [MySQL] 컬럼 한 줄 밀어서 JOIN

    테이블

    Weather

    id recordDate temperature
    1 2015-01-01 10
    2 2015-01-02 25
    3 2015-01-03 20
    4 2015-01-04 30

    문제

    • 전날 보다 온도가 오른 튜플만 뽑으시오

    답

    SELECT w2.id
FROM Weather w1, Weather w2
WHERE w1.recordDate = DATE_SUB …
    read more

  3. [MySQL] 문자함수

    CONCAT_WS

    • 붙히는 문자열 사이에 구분자를 추가
    • CONCAT_WS(구분자, str1, str2, ...)
    SELECT CONCAT_WS('/', 'one', 'two', 'three') 
=> one/two/three

    ELT

    • N번 째 문자열을 반환
    • ELT(N, str1, str2, ...)
    • 만약 N이 음수거나 …
    read more

  5. [MySQL] 날짜함수

    날짜 더하기 / 빼기

    ADDDATE / SUBDATE

    • 날짜 더하기 / 빼기
    SELECT ADDDATE('2022-01-02', INTERVAL 31 DAY) 
=> 2022-02-02

SELECT ADDDATE('2022-01-02', 31)
=> 2022-02-02

    ADDTIME / SUBTIME

    • 시간 더하기 / 빼기
    SELECT ADDTIME('2021-01-01 00:00:00', '100')
=> 2021-01-01 00:01:00

SELECT ADDTIME …
    read more

  6. [MySQL] N번 째로 높은 값

    테이블과 결과

    Input: 
Employee table:
+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+
Output: 
+---------------------+
| SecondHighestSalary |
+---------------------+
| 200                 |
+---------------------+

    문제

    • Write an SQL query to report the second highest salary from the Employee table. If there is no second highest salary, the query should report null.

    답

    SELECT …
    read more

  7. [MySQL] N번 째로 높은 값 FUNCTION

    테이블과 결과

    Input: 
Employee table:
+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+
n = 2
Output: 
+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200                    |
+------------------------+

    문제

    • Write an SQL query to report the nth highest salary from the Employee table. If there is no nth highest salary, the query should report …
    read more

  8. [MySQL] NOT IN

    테이블

    Customers 테이블

    id name
    1 Joe
    2 Henry
    3 Sam
    4 Max

    Orders

    id customerId
    1 3
    2 1

    문제

    • 아직 주문을 하지 않은 고객만 뽑으시오

    답

    SELECT c.name AS Customers 
FROM Customers …
    read more

  9. [MySQL] 피벗

    테이블과 결과

    Input: 
Department table:
+------+---------+-------+
| id   | revenue | month |
+------+---------+-------+
| 1    | 8000    | Jan   |
| 2    | 9000    | Jan   |
| 3    | 10000   | Feb   |
| 1    | 7000    | Feb   |
| 1    | 6000    | Mar   |
+------+---------+-------+
Output: 
+------+-------------+-------------+-------------+-----+-------------+
| id   | Jan_Revenue | Feb_Revenue | Mar_Revenue | ... | Dec_Revenue |
+------+-------------+-------------+-------------+-----+-------------+
| 1    | 8000        | 7000        | 6000        | ... | null        |
| 2    | 9000        | null        | null        | ... | null        |
| 3    | null        | 10000       | null        | ... | null        |
+------+-------------+-------------+-------------+-----+-------------+
Explanation: The …
    read more

links

social