SELECT 州名= case state when 'WA' then '华盛顿' when 'CA' then '加州' end, zip FROM stores
use pubs go SELECT 州名=(case state when 'WA' then '华盛顿' when 'CA' then '加州' end) ,zip FROM stores
SELECT 州名= case state when 'WA' then '华盛顿' when 'CA' then '加州' end, zip FROM stores
--或者SELECT 州名= case when state='WA' then '华盛顿' else '加州' end, zip FROM stores
SELECT 州名= case state when 'WA' then '华盛顿' when 'CA' then '加州' end, zip FROM stores
有两种语法, 1.case input_expression --变量 when when_expression1 then result_expression1 when when_expression2 then result_expression2 ... when when_expressionN then result_expressionN else else_result_expression end 叙述,case逐一测试会input_expression和when_expression1到when_expressionN比较,如果和when_expressionK相等,则执行result_expressionK,否则执行else_result_expression(如果没有else_result_expression则不执行)。 2. case when Boolean_expression1 then result_expression1 when Boolean_expression2 then result_expression2 ... when Boolean_expressionN then result_expressionN else else_result_expression end case逐一测试Boolean_expression1到Boolean_expressionN,如果Boolean_expressionK为true,则执行result_expressionK,如果都不相同,则执行else_result_expression如果没有else_result_expression则不执行)。所以你可以 1. SELECT 州名= case state when 'WA' then '华盛顿' when 'CA' then '加州' end, zip FROM stores 2. SELECT 州名= case when state = 'WA' then '华盛顿' when state = 'CA' then '加州' end, zip FROM stores
功能是一样,区别当然是有,如果你只是判断值,当然用第一种 如果是要用到判断表里面的不同情况,当然是用第二种 例如: select a_nam = (case when a_nam is not null then a_nam when b_nam is not null then b_nam else null end),... from a order by case when a_nam is not null then a_nam when b_nam is not null then b_nam else null end ---随便举的例子,你在论坛找那些汇总的问题,很多需要用到上面类似的结构
SELECT case when state='WA' then '华盛顿' when state='CA' then '加州' end 州名, zip FROM stores
SELECT 州名= case --state when state='WA' then '华盛顿' when state='CA' then '加州' end, zip FROM stores
case state
when 'WA' then '华盛顿'
when 'CA' then '加州'
end,
zip
FROM stores
go
SELECT 州名=(case state when 'WA' then '华盛顿'
when 'CA' then '加州'
end)
,zip
FROM stores
case state
when 'WA' then '华盛顿'
when 'CA' then '加州'
end,
zip
FROM stores
case when state='WA'
then '华盛顿'
else '加州'
end,
zip
FROM stores
case state
when 'WA' then '华盛顿'
when 'CA' then '加州'
end,
zip
FROM stores
1.case input_expression --变量
when when_expression1 then result_expression1
when when_expression2 then result_expression2
...
when when_expressionN then result_expressionN
else else_result_expression
end
叙述,case逐一测试会input_expression和when_expression1到when_expressionN比较,如果和when_expressionK相等,则执行result_expressionK,否则执行else_result_expression(如果没有else_result_expression则不执行)。
2.
case
when Boolean_expression1 then result_expression1
when Boolean_expression2 then result_expression2
...
when Boolean_expressionN then result_expressionN
else else_result_expression
end
case逐一测试Boolean_expression1到Boolean_expressionN,如果Boolean_expressionK为true,则执行result_expressionK,如果都不相同,则执行else_result_expression如果没有else_result_expression则不执行)。所以你可以
1.
SELECT 州名=
case state
when 'WA' then '华盛顿'
when 'CA' then '加州'
end,
zip
FROM stores
2.
SELECT 州名=
case
when state = 'WA' then '华盛顿'
when state = 'CA' then '加州'
end,
zip
FROM stores
如果是要用到判断表里面的不同情况,当然是用第二种
例如:
select a_nam = (case when a_nam is not null then a_nam
when b_nam is not null then b_nam
else null
end),...
from a
order by case when a_nam is not null then a_nam
when b_nam is not null then b_nam
else null
end
---随便举的例子,你在论坛找那些汇总的问题,很多需要用到上面类似的结构
when state='CA' then '加州'
end 州名,
zip
FROM stores
case --state
when state='WA' then '华盛顿'
when state='CA' then '加州'
end,
zip
FROM stores