【SQL干货】一条sql查出来全国空气质量排名

448 0 0

作者:buguge

-- 全国各地空气质量指数表
DROP TABLE IF EXISTS city_aqi;
CREATE TEMPORARY TABLE `city_aqi`(
`city` VARCHAR(32) COMMENT '城市/地区',
`province` VARCHAR(32) COMMENT '所在省份',
`aqi` INT COMMENT '当前AQI'
);

-- 全国各地空气质量指数

INSERT INTO city_aqi
SELECT '平果市', '广西壮族自治区', 7 UNION ALL
SELECT '隆安县', '广西壮族自治区', 7 UNION ALL
SELECT '阿克塞县', '甘肃省', 8 UNION ALL
SELECT '信阳', '河南', 10 UNION ALL
SELECT '郑州', '河南', 138 UNION ALL
SELECT '南阳', '河南', 95 UNION ALL
SELECT '呼伦贝尔', '内蒙古', 5 UNION ALL
SELECT '宁波', '浙江', 55 UNION ALL
SELECT '开封', '河南', 5 UNION ALL
SELECT '金华', '浙江', 3 UNION ALL
SELECT '防城港', '广西', 2 UNION ALL
SELECT '中山', '广东', 51 UNION ALL
SELECT '大连', '辽宁', 3 UNION ALL
SELECT '邢台', '河北省', 251 UNION ALL
SELECT '邯郸', '河北省', 200 ;

SELECT * FROM city_aqi;

-- §§§【全国空气质量排名】
SELECT city, province
, CASE WHEN aqi<50 THEN '优' WHEN aqi>=50 AND aqi<100 THEN '良' WHEN aqi>=100 AND aqi<150 THEN '中度污染' ELSE '重度污染' END AS '等级'
, aqi
FROM city_aqi
ORDER BY aqi;

-- §§§【全国空气质量排名--- 显示行号，即“排名”列】
SELECT (@i:=@i+1) AS '排名', city, province
, CASE WHEN aqi<50 THEN '优' WHEN aqi>=50 AND aqi<100 THEN '良' WHEN aqi>=100 AND aqi<150 THEN '中度污染' ELSE '重度污染' END AS '等级'
, aqi
FROM city_aqi,(SELECT @i:=0) AS it
ORDER BY aqi;

-- §§§ 倒序如果直接在order by子句里加上DESC，是不正确的。因为排名要降序，而不是从1开始。要通过临时表来绕点弯儿

SELECT * FROM(
    SELECT  (@i:=@i+1)   AS   '排名', city, province
        , CASE WHEN aqi<50 THEN '优' WHEN aqi>=50 AND aqi<100 THEN '良' WHEN aqi>=100 AND aqi<150 THEN '中度污染' ELSE '重度污染' END AS '等级'
        , aqi
    FROM city_aqi,(SELECT   @i:=0)   AS   it
    ORDER BY aqi
) a ORDER BY 1 DESC

-- 或者用如下sql 。注意不能用注释的代码了，会报错：Can't reopen table: 'city_aqi'

SELECT   @i:=COUNT(1)+1 FROM city_aqi;

SELECT  (@i:=@i-1)   AS   '排名', city, province
, CASE WHEN aqi<50 THEN '优' WHEN aqi>=50 AND aqi<100 THEN '良' WHEN aqi>=100 AND aqi<150 THEN '中度污染' ELSE '重度污染' END AS '等级'
, aqi
FROM city_aqi -- ,(SELECT   @i:= count(1) from city_aqi)   AS   it
ORDER BY aqi DESC;