SQL之ROUND、CASE、LIKE语句

SQL当中的round函数可以对数值的小数位进行处理，比如保留两位小数如下所示：

select name,ROUND(population/1000000,2)as population, ROUND(gdp/1000000000,2) as gdp
from world
where continent='South America'

还可以用来除去小数字部分，比如2134变为2000，如下所示：

select name,round(gdp/population,-3)as gdp
from world
where gdp > 1000000000000

Case的使用
如下是将continent是Oceania的替换成Australasia，其他不变：

SELECT name, 
       CASE WHEN continent='Oceania' THEN 'Australasia'
            ELSE continent END
  FROM world
 WHERE name LIKE 'N%'

如下是多个CASE使用的情况

select name,
     CASE WHEN continent='Europe' or continent='Asia' THEN'Eurasia'
          when continent='South America' or continent='North America' or continent='Caribbean' then 'America'
          else continent end
from world
where name like 'A%' or name like 'B%'

使用了CASE同时还有ORDER By 以及like：

select name,continent,
CASE when continent='Oceania' then 'Australasia'
     when continent='Eurasia' or name = 'Turkey' then 'Europe/Asia'
     when continent='Caribbean' and name like 'B%' then 'North America'
     when continent='Caribbean' and name not like 'B%' then 'South America'
     ELSE continent end
from world

order by name ASC