GROUP BY 와 HAVING
설명
GROUP BY를 사용할 때는 두가지를 기억
- 특정 컬럼을 그룹화 하는 GROUP BY
- 특정 컬럼을 그룹화한 결과에 조건을 거는 HAVING
[MySQL] 경기결과 승점 계산
테이블과 결과
read moreInput: Teams table: +-----------+--------------+ | team_id | team_name | +-----------+--------------+ | 10 | Leetcode FC | | 20 | NewYork FC | | 30 | Atlanta FC | | 40 | Chicago FC | | 50 | Toronto FC | +-----------+--------------+ Matches table: +------------+--------------+---------------+-------------+--------------+ | match_id | host_team | guest_team | host_goals | guest_goals | +------------+--------------+---------------+-------------+--------------+ | 1 | 10 | 20 | 3 | 0 | | 2 | 30 | 10 | 2 | 2 | | 3 | 10 | 50 | 5 | 1 | | 4 | 20 …[MySQL] 한 줄로 출력
테이블과 결과
read moreInput: Activities table: +------------+------------+ | sell_date | product | +------------+------------+ | 2020-05-30 | Headphone | | 2020-06-01 | Pencil | | 2020-06-02 | Mask | | 2020-05-30 | Basketball | | 2020-06-01 | Bible | | 2020-06-02 | Mask | | 2020-05-30 | T-Shirt | +------------+------------+ Output: +------------+----------+------------------------------+ | sell_date | num_sold | products | +------------+----------+------------------------------+ | 2020-05-30 | 3 | Basketball,Headphone …[MySQL] 하루 단위로 7일 동안의 매출 평균
테이블과 결과
read moreInput: Customer table: +-------------+--------------+--------------+-------------+ | customer_id | name | visited_on | amount | +-------------+--------------+--------------+-------------+ | 1 | Jhon | 2019-01-01 | 100 | | 2 | Daniel | 2019-01-02 | 110 | | 3 | Jade | 2019-01-03 | 120 | | 4 | Khaled | 2019-01-04 | 130 | | 5 | Winston | 2019-01-05 | 110 | | 6 | Elvis | 2019-01-06 | 140 | | 7 …[MySQL] IF
참고
MySQL Documentation 13.6.5.2 IF Statement
syntax
IF search_condition THEN statement_list [ELSEIF search_condition THEN statement_list] ... [ELSE statement_list] END IF예시
read moreDELIMITER // CREATE FUNCTION SimpleCompare(n INT, m INT) RETURNS VARCHAR(20) BEGIN DECLARE s VARCHAR(20); IF n > m THEN SET s = '>'; ELSEIF n = m …[MySQL] INDEX
[MySQL] JOIN
팁
가장 rows가 많은 테이블을 먼저 배치해라(쿼리 옵티마이저)
INNER JOIN
- 원래 아는 그거
OUTER JOIN
SELF JOIN
read moreSELECT p.name AS parent, c.name AS child FROM tree c, tree p WHERE …[MySQL] JOIN ON 조건 >=
테이블과 결과
read moreInput: Buses table: +--------+--------------+ | bus_id | arrival_time | +--------+--------------+ | 1 | 2 | | 2 | 4 | | 3 | 7 | +--------+--------------+ Passengers table: +--------------+--------------+ | passenger_id | arrival_time | +--------------+--------------+ | 11 | 1 | | 12 | 5 | | 13 | 6 | | 14 | 7 | +--------------+--------------+ Output: +--------+----------------+ | bus_id | passengers_cnt | +--------+----------------+ | 1 | 1 | | 2 | 0 | | 3 | 3 | +--------+----------------+ Explanation: - Passenger 11 arrives at time 1. - Bus 1 arrives at time …[MySQL] JOIN ON 조건 IN()
테이블과 결과
read moreInput: Person table: +----+----------+--------------+ | id | name | phone_number | +----+----------+--------------+ | 3 | Jonathan | 051-1234567 | | 12 | Elvis | 051-7654321 | | 1 | Moncef | 212-1234567 | | 2 | Maroua | 212-6523651 | | 7 | Meir | 972-1234567 | | 9 | Rachel | 972-0011100 | +----+----------+--------------+ Country table: +----------+--------------+ | name | country_code | +----------+--------------+ | Peru | 051 | | Israel | 972 | | Morocco | 212 | | Germany | 049 | | Ethiopia | 251 …[MySQL] 중복 제거에 조건
테이블
Person
id email 1 john@example.com 2 bob@example.com 3 john@example.com 문제
- 중복된 이메일은 제거 하되 id가 가장 낮은 튜플을 남게 하라
답
read more-- 추천 DELETE p1 FROM Person …