首页蓝耳朵|小小蓝耳朵广州图书馆外文室英语儿童读物资源介绍网佛教青年之友旧版收集永硕E盘Phonics Short Vowels Game 
case的第二种语法,根据条件表达式判断
所属栏目:MS SQL 2000  时间:2010-04-16 20:57  作者:狼子

一直以为case后面只可以用固定的值,一直在用case 变量或字段名 when 值 then 值 ... end

今天要判断正负数,搞不出来,最后用了自定义函数,小ADD刚才教了一种方法,就是case的另一种语法:case when 条件 then ... end

帮助里也有,就是下午在帮助里一直搜索select和if,没想到搜索case。。。

帮助里的第一种语法

USE pubs
GO
SELECT   Category =
      CASE type
         WHEN 'popular_comp' THEN 'Popular Computing'
         WHEN 'mod_cook' THEN 'Modern Cooking'
         WHEN 'business' THEN 'Business'
         WHEN 'psychology' THEN 'Psychology'
         WHEN 'trad_cook' THEN 'Traditional Cooking'
         ELSE 'Not yet categorized'
      END,
   CAST(title AS varchar(25)) AS 'Shortened Title',
   price AS Price
FROM titles
WHERE price IS NOT NULL
ORDER BY type, price
COMPUTE AVG(price) BY type
GO

第二种语法

USE pubs
GO
SELECT    'Price Category' =
      CASE
         WHEN price IS NULL THEN 'Not yet priced'
         WHEN price < 10 THEN 'Very Reasonable Title'
         WHEN price >= 10 and price < 20 THEN 'Coffee Table Title'
         ELSE 'Expensive book!'
      END,
   CAST(title AS varchar(20)) AS 'Shortened Title'
FROM titles
ORDER BY price
GO

我的问题用这个可以解决:

select case when InviteId > 0 then convert(bit, 1) else convert(bit, 0) end as AllowInvite from ...

小楼宝宝的涂鸦花花(Imitater)的博客起名称骨测字皖ICP备06000023号-17