"HR-SQL Weather Observation Station 20"의 두 판 사이의 차이

2018년 8월 16일 (목) 17:50 판

1 개요

HR-SQL Weather Observation Station 20

https://www.hackerrank.com/challenges/weather-observation-station-20/problem

해커랭크 SQL
문제	DB2	MS SQL	MySQL	Oracle
HR-SQL Aggregation e
HR-SQL Revising Aggregations - The Count Function
HR-SQL Revising Aggregations - The Sum Function
HR-SQL Revising Aggregations - Averages
HR-SQL Average Population
HR-SQL Japan Population
HR-SQL Population Density Difference
HR-SQL The Blunder
HR-SQL Top Earners
HR-SQL Weather Observation Station 2
HR-SQL Weather Observation Station 13
HR-SQL Weather Observation Station 14
HR-SQL Weather Observation Station 15
HR-SQL Weather Observation Station 16
HR-SQL Weather Observation Station 17
HR-SQL Weather Observation Station 18
HR-SQL Weather Observation Station 19
HR-SQL Weather Observation Station 20

2 MySQL

SELECT ROUND(LAT_N,4) FROM STATION AS S
WHERE (SELECT COUNT(*) FROM STATION WHERE LAT_N<S.LAT_N)
=(SELECT COUNT(*) FROM STATION WHERE LAT_N>S.LAT_N);
# 83.8913

SET @a=0;
SELECT ROUND(LAT_N,4)
FROM (
    SELECT @a:=@a+1 AS RN, LAT_N
    FROM STATION
    ORDER BY LAT_N
) AS S
WHERE RN=(SELECT FLOOR((COUNT(*)+1)/2) FROM STATION);
# 83.8913

SET @m=(SELECT FLOOR(COUNT(*)/2) FROM STATION);
PREPARE STMT FROM 'SELECT ROUND(LAT_N,4)
FROM STATION
ORDER BY LAT_N
LIMIT ?,1';
EXECUTE STMT USING @m;