使用 CASE |
CASE 函数是特殊的 Transact-SQL 表达式,它允许按列值显式可选值。数据中的更改是临时的,没有对数据进行永久更改。例如,CASE 函数可以在 state 列中有 CA 值的行的查询结果集内显示 California。 |
CASE 函数包含: |
CASE 关键字。 |
需要转换的列名称。 |
指定要搜索的表达式的 WHEN 子句和指定要替换它们的表达式的 THEN 子句。 |
END 关键字。 |
可选的、定义 CASE 函数别名的 AS 子句。 |
下面示例在查询结果集内显示每个作者所居住州的全名: |
SELECT au_fname, au_lname, |
CASE state |
WHEN 'CA ' THEN 'California ' |
WHEN 'KS ' THEN 'Kansas ' |
WHEN 'TN ' THEN 'Tennessee ' |
WHEN 'OR ' THEN 'Oregon ' |
WHEN 'MI ' THEN 'Michigan ' |
WHEN 'IN ' THEN 'Indiana ' |
WHEN 'MD ' THEN 'Maryland ' |
WHEN 'UT ' THEN 'Utah ' |
END AS StateName |
FROM pubs.dbo.authors |
ORDER BY au_lname |
CASE |
计算条件列表并返回多个可能结果表达式之一。 |
CASE 具有两种格式: |
简单 CASE 函数将某个表达式与一组简单表达式进行比较以确定结果。 |
CASE 搜索函数计算一组布尔表达式以确定结果。 |
两种格式都支持可选的 ELSE 参数。 |
语法 |
简单 CASE 函数: |
CASE input_expression |
WHEN when_expression THEN result_expression |
[ ...n ] |
[ |
ELSE else_result_expression |
END |
CASE 搜索函数: |
CASE |
WHEN Boolean_expression THEN result_expression |
[ ...n ] |
[ |
ELSE else_result_expression |
END |
参数 |
input_expression |
是使用简单 CASE 格式时所计算的表达式。Input_expression 是任何有效的 Microsoft® SQL Server™ 表达式。 |
WHEN when_expression |
使用简单 CASE 格式时 input_expression 所比较的简单表达式。When_expression 是任意有效的 SQL Server 表达式。Input_expression 和每个 when_expression 的数据类型必须相同,或者是隐性转换。 |
n |
占位符,表明可以使用多个 WHEN when_expression THEN result_expression 子句或 WHEN Boolean_expression THEN result_expression 子句。 |
THEN result_expression |
当 input_expression = when_expression 取值为 TRUE,或者 Boolean_expression 取值为 TRUE 时返回的表达式。result expression 是任意有效的 SQL Server 表达式。 |
ELSE else_result_expression |
当比较运算取值不为 TRUE 时返回的表达式。如果省略此参数并且比较运算取值不为 TRUE,CASE 将返回 NULL 值。Else_result_expression 是任意有效的 SQL Server 表达式。Else_result_expression 和所有 result_expression 的数据类型必须相同,或者必须是隐性转换。 |
WHEN Boolean_expression |
使用 CASE 搜索格式时所计算的布尔表达式。Boolean_expression 是任意有效的布尔表达式。 |
结果类型 |
从 result_expressions 和可选 else_result_expression 的类型集合中返回最高的优先规则类型。有关更多信息,请参见数据类型的优先顺序。 |
结果值 |
简单 CASE 函数: |
计算 input_expression,然后按指定顺序对每个 WHEN 子句的 input_expression = when_expression 进行计算。 |
返回第一个取值为 TRUE 的 (input_expression = when_expression) 的 result_expression。 |
如果没有取值为 TRUE 的 input_expression = when_expression,则当指定 ELSE 子句时 SQL Server 将返回 else_result_expression;若没有指定 ELSE 子句,则返回 NULL 值。 |
CASE 搜索函数: |
按指定顺序为每个 WHEN 子句的 Boolean_expression 求值。 |
返回第一个取值为 TRUE 的 Boolean_expression 的 result_expression。 |
如果没有取值为 TRUE 的 Boolean_expression,则当指定 ELSE 子句时 SQL Server 将返回 else_result_expression;若没有指定 ELSE 子句,则返回 NULL 值。 |
示例 |
A. 使用带有简单 CASE 函数的 SELECT 语句 |
在 SELECT 语句中,简单 CASE 函数仅检查是否相等,而不进行其它比较。下面的示例使用 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 |
|
下面是结果集: |
|
Category Shortened Title Price |
------------------- ------------------------- -------------------------- |
Business You Can Combat Computer S 2.99 |
Business Cooking with Computers: S 11.95 |
Business The Busy Executive's Data 19.99 |
Business Straight Talk About Compu 19.99 |
|
avg |
========================== |
13.73 |
|
Category Shortened Title Price |
------------------- ------------------------- -------------------------- |
Modern Cooking The Gourmet Microwave 2.99 |
Modern Cooking Silicon Valley Gastronomi 19.99 |
|
avg |
========================== |
11.49 |
|
Category Shortened Title Price |
------------------- ------------------------- -------------------------- |
Popular Computing Secrets of Silicon Valley 20.00 |
Popular Computing But Is It User Friendly? 22.95 |
|
avg |
========================== |
21.48 |
|
Category Shortened Title Price |
------------------- ------------------------- -------------------------- |
Psychology Life Without Fear 7.00 |
Psychology Emotional Security: A New 7.99 |
Psychology Is Anger the Enemy? 10.95 |
Psychology Prolonged Data Deprivatio 19.99 |
Psychology Computer Phobic AND Non-P 21.59 |
|
avg |
========================== |
13.50 |
|
Category Shortened Title Price |
------------------- ------------------------- -------------------------- |
Traditional Cooking Fifty Years in Buckingham 11.95 |
Traditional Cooking Sushi, Anyone? 14.99 |
Traditional Cooking Onions, Leeks, and Garlic 20.95 |
|
avg |
========================== |
15.96 |
(21 row(s) affected) |
B. 使用带有简单 CASE 函数和 CASE 搜索函数的 SELECT 语句 |
在 SELECT 语句中,CASE 搜索函数允许根据比较值在结果集内对值进行替换。下面的示例根据图书的价格范围将价格(money 列)显示为文本注释。 |
|
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 |
|
下面是结果集: |
|
Price Category Shortened Title |
--------------------- -------------------- |
Not yet priced Net Etiquette |
Not yet priced The Psychology of Co |
Very Reasonable Title The Gourmet Microwav |
Very Reasonable Title You Can Combat Compu |
Very Reasonable Title Life Without Fear |
Very Reasonable Title Emotional Security: |
Coffee Table Title Is Anger the Enemy? |
Coffee Table Title Cooking with Compute |
Coffee Table Title Fifty Years in Bucki |
Coffee Table Title Sushi, Anyone? |
Coffee Table Title Prolonged Data Depri |
Coffee Table Title Silicon Valley Gastr |
Coffee Table Title Straight Talk About |
Coffee Table Title The Busy Executive's |
Expensive book! Secrets of Silicon V |
Expensive book! Onions, Leeks, and G |
Expensive book! Computer Phobic And |
Expensive book! But Is It User Frien |
|
(18 row(s) affected) |
|
C. 使用带有 SUBSTRING 和 SELECT 的 CASE 函数 |
下面的示例使用 CASE 和 THEN 生成一个有关作者、图书标识号和每个作者所著图书类型的列表。 |
|
USE pubs |
SELECT SUBSTRING((RTRIM(a.au_fname) + ' ' + |
RTRIM(a.au_lname) + ' ' ), 1, 25) AS Name, a.au_id, ta.title_id, |
Type = |
CASE |
WHEN SUBSTRING(ta.title_id, 1, 2) = 'BU' THEN 'Business' |
WHEN SUBSTRING(ta.title_id, 1, 2) = 'MC' THEN 'Modern Cooking' |
WHEN SUBSTRING(ta.title_id, 1, 2) = 'PC' THEN 'Popular Computing' |
WHEN SUBSTRING(ta.title_id, 1, 2) = 'PS' THEN 'Psychology' |
WHEN SUBSTRING(ta.title_id, 1, 2) = 'TC' THEN 'Traditional Cooking' |
END |
FROM titleauthor ta JOIN authors a ON ta.au_id = a.au_id |
|
下面是结果集: |
|
Name au_id title_id Type |
------------------------- ----------- -------- ------------------- |
Johnson White 172-32-1176 PS3333 Psychology |
Marjorie Green 213-46-8915 BU1032 Business |
Marjorie Green 213-46-8915 BU2075 Business |
Cheryl Carson 238-95-7766 PC1035 Popular Computing |
Michael O'Leary 267-41-2394 BU1111 Business |
Michael O'Leary 267-41-2394 TC7777 Traditional Cooking |
Dean Straight 274-80-9391 BU7832 Business |
Abraham Bennet 409-56-7008 BU1032 Business |
Ann Dull 427-17-2319 PC8888 Popular Computing |
Burt Gringlesby 472-27-2349 TC7777 Traditional Cooking |
Charlene Locksley 486-29-1786 PC9999 Popular Computing |
Charlene Locksley 486-29-1786 PS7777 Psychology |
Reginald Blotchet-Halls 648-92-1872 TC4203 Traditional Cooking |
Akiko Yokomoto 672-71-3249 TC7777 Traditional Cooking |
Innes del Castillo 712-45-1867 MC2222 Modern Cooking |
Michel DeFrance 722-51-5454 MC3021 Modern Cooking |
Stearns MacFeather 724-80-9391 BU1111 Business |
Stearns MacFeather 724-80-9391 PS1372 Psychology |
Livia Karsen 756-30-7391 PS1372 Psychology |
Sylvia Panteley 807-91-6654 TC3218 Traditional Cooking |
Sheryl Hunter 846-92-7186 PC8888 Popular Computing |
Anne Ringer 899-46-2035 MC3021 Modern Cooking |
Anne Ringer 899-46-2035 PS2091 Psychology |
Albert Ringer 998-72-3567 PS2091 Psychology |
Albert Ringer 998-72-3567 PS2106 Psychology |
|
(25 row(s) affected) |
主要应用case语句来解决行转列的问题 行转列问题主要分为两类 1 )简单的行转列问题: 示例表: id sid course result 1 2005001 语文 80.0 2 2005001 数学 90.0 3 2005001 英语 80.0 4 2005002 语文 56.0 5 2005002 数学 69.0 6 2005002 英语 89.0 执行 select sid,语文 = isnull(sum( case course when ' 语文 ' then result end), 0 ), 数学 = isnull(sum( case course when ' 数学 ' then result end), 0 ), 英语 = isnull(sum( case course when ' 英语 ' then result end), 0 ) from result group by sid order by sid 得出结果 sid 语文 数学 英语 2005001 80.0 90.0 80.0 2005002 56.0 69.0 89.0 2 )较为复杂的行转列 表1:course id name 1 语文 2 数学 3 英语 表2:result id sid course result 1 2005001 语文 80.0 2 2005001 数学 90.0 3 2005001 英语 80.0 4 2005002 语文 56.0 5 2005002 数学 69.0 6 2005002 英语 89.0 declare @sql varchar( 8000 ) set @sql = ' select sid ' select @sql = @sql + ' , ' + course.name + ' =isnull(sum(case course when ''' + course.name + ''' then result end),0) ' from course order by id set @sql = @sql + ' from result group by sid order by sid ' print @sql exec(@sql) 得出结果 sid 语文 数学 英语 2005001 80.0 90.0 80.0 2005002 56.0 69.0 89.0 本文来自CSDN博客,转载请标明出处:http: // blog.csdn.net/HelloXiaoyu/archive/2009/01/19/3835549.aspx
Case具有两种格式。简单Case函数和Case搜索函数。 |
--简单Case函数 |
CASE sex |
WHEN '1' THEN '男' |
WHEN '2' THEN '女' |
ELSE '其他' END |
--Case搜索函数 |
CASE WHEN sex = '1' THEN '男' |
WHEN sex = '2' THEN '女' |
ELSE '其他' END |
|
这两种方式,可以实现相同的功能。简单Case函数的写法相对比较简洁,但是和Case搜索函数相比,功能方面会有些限制,比如写判断式。 |
还有一个需要注意的问题,Case函数只返回第一个符合条件的值,剩下的Case部分将会被自动忽略。 |
|
--比如说,下面这段SQL,你永远无法得到“第二类”这个结果 |
CASE WHEN col_1 IN ( 'a' , 'b' ) THEN '第一类' |
WHEN col_1 IN ( 'a' ) THEN '第二类' |
ELSE '其他' END |
下面我们来看一下,使用Case函数都能做些什么事情。 |
|
一,已知数据按照另外一种方式进行分组,分析。 |
|
有如下数据:(为了看得更清楚,我并没有使用国家代码,而是直接用国家名作为Primary Key) |
国家(country) 人口(population) |
中国 600 |
美国 100 |
加拿大 100 |
英国 200 |
法国 300 |
日本 250 |
德国 200 |
墨西哥 50 |
印度 250 |
|
根据这个国家人口数据,统计亚洲和北美洲的人口数量。应该得到下面这个结果。 |
洲 人口 |
亚洲 1100 |
北美洲 250 |
其他 700 |
|
想要解决这个问题,你会怎么做?生成一个带有洲Code的View,是一个解决方法,但是这样很难动态的改变统计的方式。 |
如果使用Case函数,SQL代码如下: |
SELECT SUM(population), |
CASE country |
WHEN '中国' THEN '亚洲' |
WHEN '印度' THEN '亚洲' |
WHEN '日本' THEN '亚洲' |
WHEN '美国' THEN '北美洲' |
WHEN '加拿大' THEN '北美洲' |
WHEN '墨西哥' THEN '北美洲' |
ELSE '其他' END |
FROM Table_A |
GROUP BY CASE country |
WHEN '中国' THEN '亚洲' |
WHEN '印度' THEN '亚洲' |
WHEN '日本' THEN '亚洲' |
WHEN '美国' THEN '北美洲' |
WHEN '加拿大' THEN '北美洲' |
WHEN '墨西哥' THEN '北美洲' |
ELSE '其他' END; |
|
同样的,我们也可以用这个方法来判断工资的等级,并统计每一等级的人数。SQL代码如下; |
|
SELECT |
CASE WHEN salary <= 500 THEN '1' |
WHEN salary > 500 AND salary <= 600 THEN '2' |
WHEN salary > 600 AND salary <= 800 THEN '3' |
WHEN salary > 800 AND salary <= 1000 THEN '4' |
ELSE NULL END salary_class, |
COUNT(*) |
FROM Table_A |
GROUP BY |
CASE WHEN salary <= 500 THEN '1' |
WHEN salary > 500 AND salary <= 600 THEN '2' |
WHEN salary > 600 AND salary <= 800 THEN '3' |
WHEN salary > 800 AND salary <= 1000 THEN '4' |
ELSE NULL END; |
|
二,用一个SQL语句完成不同条件的分组。 |
|
有如下数据 |
国家(country) 性别(sex) 人口(population) |
中国 1 340 |
中国 2 260 |
美国 1 45 |
美国 2 55 |
加拿大 1 51 |
加拿大 2 49 |
英国 1 40 |
英国 2 60 |
|
按照国家和性别进行分组,得出结果如下 |
国家 男 女 |
中国 340 260 |
美国 45 55 |
加拿大 51 49 |
英国 40 60 |
|
普通情况下,用UNION也可以实现用一条语句进行查询。但是那样增加消耗(两个Select部分),而且SQL语句会比较长。 |
下面是一个是用Case函数来完成这个功能的例子 |
|
SELECT country, |
SUM( CASE WHEN sex = '1' THEN |
population ELSE 0 END), --男性人口 |
SUM( CASE WHEN sex = '2' THEN |
population ELSE 0 END) --女性人口 |
FROM Table_A |
GROUP BY country; |
|
这样我们使用Select,完成对二维表的输出形式,充分显示了Case函数的强大。 |
|
三,在Check中使用Case函数。 |
|
在Check中使用Case函数在很多情况下都是非常不错的解决方法。可能有很多人根本就不用Check,那么我建议你在看过下面的例子之后也尝试一下在SQL中使用Check。 |
下面我们来举个例子 |
公司A,这个公司有个规定,女职员的工资必须高于1000块。如果用Check和Case来表现的话,如下所示 |
CONSTRAINT check_salary CHECK |
( CASE WHEN sex = '2' |
THEN CASE WHEN salary > 1000 |
THEN 1 ELSE 0 END |
ELSE 1 END = 1 ) |
|
如果单纯使用Check,如下所示 |
|
CONSTRAINT check_salary CHECK |
( sex = '2' AND salary > 1000 ) |
|
女职员的条件倒是符合了,男职员就无法输入了 |
近期做一个项目,实现不同条件下的不同补助类型的计算,由于后续补助的类型,金额都有可能有大的改动,考虑再三决定全部在sql server存储过程中实现,为后续的修改留足空间和余地,其中用到 case ....when语句,感觉功能挺强大的,对付很多有复杂逻辑的操作,不失是一种好的解决办法,代码如下,大家可以参考语法规则,看一看,希望对大家的学习有帮助. |
|
CREATE PROCEDURE [dbo].[AddBZmoney] AS---确定就餐类型和补助金额 |
|
insert into SumBZ(卡号,日期,餐别,就餐金额)--汇总每日就餐明细 |
|
SELECT Detail.卡号, Detail.就餐日期, dbo.GetBZType(Detail.就餐时间) AS 餐别, --查询就餐金额 |
SUM(Detail.消费金额) AS 消费金额 |
FROM Detail INNER JOIN |
BZType ON dbo.GetBZType(Detail.就餐时间) = BZType.餐别 |
WHERE (Detail.状态 = 0) |
GROUP BY Detail.就餐日期, dbo.GetBZType(Detail.就餐时间), Detail.卡号 |
|
update Detail set 状态=1 where 状态=0--更新补助状态 |
|
create table TempBZ--建立中间表 |
( |
ID int , |
BZmoney money |
) |
|
insert into TempBZ |
SELECT SumBZ.ID, |
CASE |
WHEN SumBZ.就餐金额 >= BZType.补助标准 and People.加班补助=1 and SumBZ.餐别= '晚餐' THEN BZType.补助标准--晚餐有加班补助的,超过4元补4元 |
when SumBZ.就餐金额 >= BZType.补助标准 and People.加班补助=0 and SumBZ.餐别= '晚餐' then 0--晚餐无加班补助的,补0元 |
when SumBZ.就餐金额 >= BZType.补助标准 and SumBZ.餐别= '午餐' then BZType.补助标准--午餐全部超过3元的,补3元 |
when SumBZ.餐别= '早餐' then BZType.补助标准--早餐不补 |
WHEN SumBZ.就餐金额 < BZType.补助标准 THEN 0--未超过补助额度的不补 |
end |
FROM SumBZ INNER JOIN |
BZType ON SumBZ.餐别 = BZType.餐别 inner join People On People.卡号=SumBZ.卡号 |
|
declare @i int |
set @i=(select min(ID) from TempBZ) |
while @i<=(select max(ID) from TempBZ) |
begin |
update SumBZ set 补助金额=(select BZmoney from TempBZ where ID=@i) where ID=@i |
set @i=@i+1 |
end |
|
drop table TempBZ |
|
另附上函数,根据就餐时间,判断餐别 |
|
CREATE FUNCTION [dbo].[GetBZType] ( @InputTime as datetime ) -- 确定就餐餐别 |
RETURNS nvarchar(10) AS |
begin |
declare @BZType as nvarchar(10)--输入就餐时间返回就餐类别:早餐,中餐,晚餐 |
BEGIN |
declare @StartTime datetime--开始时间 |
declare @EndTime datetime --结束时间 |
declare @i int |
set @i=1 |
while @i<=(SELECT COUNT(*) FROM BZType) |
begin |
SELECT @StartTime=开始时间, @EndTime =结束时间, @BZType=餐别 FROM BZType where ID=@i |
if (@InputTime>= @StartTime and @InputTime<=@EndTime)-- 如果在规定时间内,确定就餐餐别 |
return @BZType |
set @i=@i+1 |
END |
return @BZType |
end |
end |
|
1:有member表(m_id int ,m_sex varchar(20))其中m_sex有三个不同值( '男' , '女' , 'null' ), |
要求用一条SQL语句把member表的m_sex字段更新成m_sex的值( '男' )变成 '女' , |
'女' 变成 '男' , 'null' 不变; |
答:update member |
set m_sex = case m_sex when '女' then '男' when '男' then '女' else 'null' |
end |
|
2:有一张表table1,有三个字段分别是:年度,季度,数量;现在数据如下: |
年度,季度,数量 |
1991 1 23 |
1991 2 32 |
1991 3 15 |
1991 4 20 |
1992 1 25 |
1992 2 65 |
要求用一条SQL语句查出结果为: |
年份 1季度 2季度 3季度 4季度 |
1991 23 32 15 20 |
1992 25 65 0 0 |
答:select b.Year as '年份' ,sum(b.quarter1) as '1季度' ,sum(b.quarter2) as '2季度' , |
sum(b.quarter3) as '3季度' ,sum(b.quarter4) as '4季度' from( |
select a.Year, |
case a.quarter when 1 then a.Nums else 0 end as quarter1, |
case a.quarter when 2 then a.Nums else 0 end as quarter2, |
case a.quarter when 3 then a.Nums else 0 end as quarter3, |
case a.quarter when 4 then a.Nums else 0 end as quarter4 |
from table1 as a)b |
group by b.Year |
select (case Power when 'A' then '員工' when 'B' then '管理員' when 'C' then '系統管理員' end)as c0
FROM dbo.employee
2、项目中,联系人详细信息这一个页面中,联系人的性别在数据库字段的保存,男的用0表示,女的用1表示,所以,如果直接就读出来绑定到DataList中,性别就显示成0或1了。所以需要将Sql语句进行改造。先看看我原先的做法,这样做,组长叫我重做了,要叫我要CASE WHEN来写。 |
|
不用CASE WHEN 的做法。源代码如下: |
|
string sql= "select * from CONTACTPERSON ,CUSTOMER where CONTACTPERSONID='" +strContactid+ "' and CONTACTPERSON.CUSTOMERID=CUSTOMER.CUSTOMERID" ; |
DataSet ds= new DataSet(); |
ds=Common.GetDSCommon(sql); |
|
if (ds.Tables[0].Rows[0][ "CONTACTPERSONSEX" ].ToString()== "0" ) |
{ |
ds.Tables[0].Rows[0][ "CONTACTPERSONSEX" ]= "男" ; |
} |
else |
{ |
ds.Tables[0].Rows[0][ "CONTACTPERSONSEX" ]= "女" ; |
} |
|
使用CASE WHEN做法。 |
|
string sql= "select CUSTOMER.*,CONTACTPERSON.CONTACTPERSONNAME,CONTACTPERSON.CONTACTPERSONBIRTHDAY,CONTACTPERSON.CONTACTPERSONPOSITION,CONTACTPERSON.CONTACTPERSONDEPTNAME," ; |
sql+= "CONTACTPERSON.TELEPHONE,CONTACTPERSON.MAIL,CONTACTPERSON.CITY,CONTACTPERSON.PROVINCE,CONTACTPERSON.POSTCODE,CONTACTPERSON.COUNTRY,CONTACTPERSON.MEMO," ; |
sql+= "CONTACTPERSON.DIRECTOR," ; |
|
sql+= "case when CONTACTPERSON.CONTACTPERSONSEX = '0'then '男' when CONTACTPERSON.CONTACTPERSONSEX= '1' then '女' end as CONTACTPERSONSEX from CUSTOMER ,CONTACTPERSON where CONTACTPERSONID='" +strContactid+ "' and CONTACTPERSON.CUSTOMERID=CUSTOMER.CUSTOMERID" ; |
可以看出这样明显加长了,SQL语句,这样做有什么好处呢? |
|
3、SQL条件控制( case when...then... else ...end) |
|
我的语句:(SQL 2000) |
1: |
select ByeTime, case when len(ByeTime)>0 then str(DATEDIFF(day,ComeInTime,ByeTime)) else DATEDIFF(day,ComeInTime,getdate()) end as test |
from myUser where DelFlag= '0' and UserID= 'cq' |
2: |
select ByeTime, case when len(ByeTime)>0 then str(DATEDIFF(day,ComeInTime,ByeTime))+ '(已离职)' end as test |
from myUser where DelFlag= '0' and UserID= 'cq' |
3: |
select ByeTime, case when len(ByeTime)>0 then str(DATEDIFF(day,ComeInTime,ByeTime))+ '(已离职)' |
else DATEDIFF(day,ComeInTime,getdate()) end as test |
from myUser where DelFlag= '0' and UserID= 'cq' |
|
为什么1 和2 执行都没有问题,3 却报“将 varchar 值 ' 480(已离职)' 转换为数据类型为 int 的列时发生语法错误。” |
|
|
select ByeTime, case when len(ByeTime)>0 then str(DATEDIFF(day,ComeInTime,ByeTime))+ '(已离职)' |
else str(DATEDIFF(day,ComeInTime,getdate())) end as test |
from myUser where DelFlag= '0' and UserID= 'cq' |
这样试一下.语法应该没有问题 |
|
4、WHEN THEN |
WHEN THEN |
ELSE |
END |
-------------- |
select count(*) as ct , CASE |
WHEN (Pro_state= 1) THEN '待审' |
WHEN (Pro_state= 2) THEN '已审' |
WHEN (Pro_state= 3) THEN '办理中' |
WHEN (Pro_state= 4) THEN '已申退' |
WHEN (Pro_state= 5) THEN '来信' |
WHEN (Pro_state= 6) THEN '办理完成' |
WHEN (Pro_state= 7) THEN '未予立案' |
end as pro_state |
from vw_proposals_query where 1=1 GROUP BY pro_state |
|
结果: |
ct pro-state |
30 办理中 |
7 来信 |
1 办理完成 |
5 未予立案 |
|
解读: |
查询总数和pro_state 当 Pro_state= 1 时以 '待审' 替代表示 |
当 Pro_state= 2 时以 '已审' 替代表示 |
当 Pro_state= 3 时以 '办理中' 替代表示 |
。。。。。。 |
|
5、CASE 可能是 SQL 中被误用最多的关键字之一。虽然你可能以前用过这个关键字来创建字段,但是它还具有更多用法。例如,你可以在 WHERE 子句中使用 CASE。 |
|
首先让我们看一下 CASE 的语法。在一般的 SELECT 中,其语法如下: |
|
SELECT <myColumnSpec> = |
CASE |
WHEN <A> THEN <somethingA> |
WHEN <B> THEN <somethingB> |
ELSE <somethingE> |
END |
|
在上面的代码中需要用具体的参数代替尖括号中的内容。下面是一个简单的例子: |
|
USE pubs |
GO |
SELECT |
Title, |
'Price Range' = |
CASE |
WHEN price IS NULL THEN 'Unpriced' |
WHEN price < 10 THEN 'Bargain' |
WHEN price BETWEEN 10 and 20 THEN 'Average' |
ELSE 'Gift to impress relatives' |
END |
FROM titles |
ORDER BY price |
GO |
|
这是 CASE 的典型用法,但是使用 CASE 其实可以做更多的事情。比方说下面的 GROUP BY 子句中的 CASE: |
|
SELECT 'Number of Titles' , Count(*) |
FROM titles |
GROUP BY |
CASE |
WHEN price IS NULL THEN 'Unpriced' |
WHEN price < 10 THEN 'Bargain' |
WHEN price BETWEEN 10 and 20 THEN 'Average' |
ELSE 'Gift to impress relatives' |
END |
GO |
|
你甚至还可以组合这些选项,添加一个 ORDER BY 子句,如下所示: |
|
USE pubs |
GO |
SELECT |
CASE |
WHEN price IS NULL THEN 'Unpriced' |
WHEN price < 10 THEN 'Bargain' |
WHEN price BETWEEN 10 and 20 THEN 'Average' |
ELSE 'Gift to impress relatives' |
END AS Range, |
Title |
FROM titles |
GROUP BY |
CASE |
WHEN price IS NULL THEN 'Unpriced' |
WHEN price < 10 THEN 'Bargain' |
WHEN price BETWEEN 10 and 20 THEN 'Average' |
ELSE 'Gift to impress relatives' |
END, |
Title |
ORDER BY |
CASE |
WHEN price IS NULL THEN 'Unpriced' |
WHEN price < 10 THEN 'Bargain' |
WHEN price BETWEEN 10 and 20 THEN 'Average' |
ELSE 'Gift to impress relatives' |
END, |
Title |
GO |
|
注意,为了在 GROUP BY 块中使用 CASE,查询语句需要在 GROUP BY 块中重复 SELECT 块中的 CASE 块。 |
|
除了选择自定义字段之外,在很多情况下 CASE 都非常有用。再深入一步,你还可以得到你以前认为不可能得到的分组排序结果集。 |
|
|
|
ISNULL |
使用指定的替换值替换 NULL。 |
|
语法 |
ISNULL ( check_expression , replacement_value ) |
|
参数 |
check_expression |
|
将被检查是否为 NULL的表达式。check_expression 可以是任何类型的。 |
|
replacement_value |
|
在 check_expression 为 NULL时将返回的表达式。replacement_value 必须与 check_expresssion 具有相同的类型。 |
|
返回类型 |
返回与 check_expression 相同的类型。 |
|
注释 |
如果 check_expression 不为 NULL,那么返回该表达式的值;否则返回 replacement_value。 |
|
示例 |
A. 将 ISNULL 与 AVG 一起使用 |
下面的示例查找所有书的平均价格,用值 $10.00 替换 titles 表的 price 列中的所有 NULL 条目。 |
|
USE pubs |
GO |
SELECT AVG(ISNULL(price, $10.00)) |
FROM titles |
GO |
|
下面是结果集: |
|
-------------------------- |
14.24 |
|
(1 row(s) affected) |
|
B. 使用 ISNULL |
下面的示例为 titles 表中的所有书选择书名、类型及价格。如果一个书名的价格是 NULL,那么在结果集中显示的价格为 0.00。 |
|
USE pubs |
GO |
SELECT SUBSTRING(title, 1, 15) AS Title, type AS Type, |
ISNULL(price, 0.00) AS Price |
FROM titles |
GO |
|
C. 在Full Join情况下使用ISNULL |
表A: |
tid |
uid |
anum |
|
表B: |
tbid |
uid |
bnum1 |
bnum2 |
需要通过uid全连接两个表: |
select a.tid,a.uid,a.anum,b.bnum1,b.bnum2 from a full join b on a.uid=b.uid |
全连接会有很多为空的情况,可以使用ISNull来解决,改为: |
select isnull(a.tid,b.tid),isnull(a.uid,b.uid),isnull(a.anum,0),isnull(b.bnum1,0),isnull(b.bnum2,0) from a full join b on a.uid=b.uid |
|
前部分引用SQL参考手册(一直漂注) |