使用 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] ( @InputTimeas datetime ) -- 确定就餐餐别 |
RETURNS nvarchar(10) AS |
begin |
declare @BZType asnvarchar(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 |
setm_sex =casem_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.Yearas '年份',sum(b.quarter1)as '1季度',sum(b.quarter2)as '2季度', |
sum(b.quarter3)as '3季度',sum(b.quarter4)as '4季度'from( |
select a.Year, |
casea.quarter when 1 then a.Nums else0 end as quarter1, |
casea.quarter when 2 then a.Nums else0 end as quarter2, |
casea.quarter when 3 then a.Nums else0 end as quarter3, |
casea.quarter when 4 then a.Nums else0 end as quarter4 |
from table1as 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=newDataSet(); |
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条件控制（casewhen...then...else...end） |
|
我的语句：（SQL 2000） |
1: |
select ByeTime,casewhen len(ByeTime)>0 then str(DATEDIFF(day,ComeInTime,ByeTime))else DATEDIFF(day,ComeInTime,getdate()) endas test |
from myUser where DelFlag='0'and UserID='cq' |
2: |
select ByeTime,casewhen len(ByeTime)>0 then str(DATEDIFF(day,ComeInTime,ByeTime))+'(已离职)'end as test |
from myUser where DelFlag='0'and UserID='cq' |
3: |
select ByeTime,casewhen len(ByeTime)>0 then str(DATEDIFF(day,ComeInTime,ByeTime))+'(已离职)' |
else DATEDIFF(day,ComeInTime,getdate()) end astest |
from myUser where DelFlag='0'and UserID='cq' |
|
为什么1 和2 执行都没有问题,3 却报“将 varchar 值 ' 480(已离职)' 转换为数据类型为int 的列时发生语法错误。” |
|
|
select ByeTime,casewhen len(ByeTime)>0 then str(DATEDIFF(day,ComeInTime,ByeTime))+'(已离职)' |
else str(DATEDIFF(day,ComeInTime,getdate())) end astest |
from myUser where DelFlag='0'and UserID='cq' |
这样试一下.语法应该没有问题 |
|
4、WHEN THEN |
WHEN THEN |
ELSE |
END |
-------------- |
select count(*) asct , 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 aspro_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参考手册(一直漂注) |
3042
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
