case的第二种语法,根据条件表达式判断
一直以为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 ...