SQL : 다중행함수, UNION, UNION ALL

-- 다중행함수
USE world;
SELECT SUM(population) AS sum_popul, SUM(surfacearea) AS sum_surface
FROM country;

여기서 다른 행과 함께쓰면 오류남 예, select code, sum(population)... 하면 오류남

 

-- 행추가하는법
SELECT 'KOR' AS code,MAX(population) AS maxp, MIN(population) AS minp, MIN(population)/MAX(population)*100 as rate
FROM city
WHERE countrycode LIKE 'KOR';

-- 여기서 'KOR' 열은 추가된것. 나머지는 다중행함수.

-- 출력결과 두개 결합해 보여주기: UNION
SELECT 'KOR',MAX(population) AS maxp, MIN(population) AS minp, MIN(population)/MAX(population)*100 as rate
FROM city
WHERE countrycode LIKE 'KOR'
UNION
SELECT 'JPN',MAX(population) AS maxp, MIN(population) AS minp, MIN(population)/MAX(population)*100 as rate
FROM city;

-- 단 UNION은 중복제거해서 결과를 보여줌
-- 중복 제거없이 결과 다 보여주려면 UNION ALL

SELECT * FROM country
UNION ALL
SELECT * FROM city
ORDER BY code;

 

* order by와 limit을 쓰고나면 그 아래에는 union을 쓸수없음