-- 全國各地空氣質量指數表
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/413705.html
標籤：Oracle