SQL学习笔记二

1.条件判断函数case

case when expr1 then v1 [when expr2 then v2……][else vn] end

case表示函数开始，end表示函数结束。如果表达式expre1成立，则返回v1值；如果表达式expr2成立，则返回v2值；以此类推，最后遇到else时，返回vn值。他的功能与PHP中的switch语句类似

case expr when e1 then v1 [when e2 then v2 ……][else vn] end

case表示函数开始，end表示函数结束。如果表达式expr取值为e1，则返回v1值，如果表达式expr取值为e2，则返回v2值，以此类推，最后遇到else，则返回vn值

例1：

Show the name - but substitute Australasia for Oceania - for countries beginning with N.

SELECT name, continent,

       CASE WHEN continent='Oceania'  THEN 'Australasia'

                 ELSE continent END
  FROM world

 WHERE name LIKE 'N%'

例2：

Show the name and the continent - but substitute Eurasia for Europe and Asia; substitute America - for each country in North America or South America or Caribbean. Show countries beginning with A or B.

select name,

case when continent in('Europe','Asia') then 'Eurasia'

         when continent in('North America','South America','Caribbean') then 'America'

else continent end

from world where name like 'A%' or name like 'B%'

例3：

Put the continents right...

• Oceania becomes Australasia
• Countries in Eurasia and Turkey go to Europe/Asia
• Caribbean islands starting with 'B' go to North America, other Caribbean islands go to South America

Show the name, the original continent and the new continent of all countries.

select name,continent,

case when continent in('Eurasia ','Turkey') then 'Europe/Asia'

         when continent = 'Oceania' then 'Australasia'

         when continent = 'Caribbean' then 

                   case when name like 'B%' then 'North America' else 'South America' end

         else continent end

from world order by name ASC