casewhen几个简单示例

简单Case函数

CASEsex

WHEN1THEN男

WHEN2THEN女

ELSE其他END

selectCASEState

WHEN0THEN未完成

WHEN1THEN完成

ELSEENDAS状态

fromflowrun

selectgetdate()as日期,casemonth(getdate())

when11then十一

when12then十二

elsesubstring(一二三四五六七八九十,month(getdate()),1)

end+月as月份

Case搜索函数

CASE

WHENsex=1THEN男

WHENsex=2THEN女

ELSE其他

END

casewhen更多用法

CASE可能是SQL中被误用最多的关键字之一。

虽然你可能以前用过这个关键字来创建字段,但是它还具有更多用法。

例如,你可以在WHERE子句中使用CASE。

首先让我们看一下CASE的语法。

在一般的SELECT中,其语法如下:

SELECTmyColumnSpec=

CASE

WHENATHENsomethingA

WHENBTHENsomethingB

ELSEsomethingE

END

在上面的代码中需要用具体的参数代替尖括号中的内容。

下面是一个简单的例子:

USEpubs

GO

SELECT

Title,

PriceRange=

CASE

WHENpriceISNULLTHENUnpriced

WHENprice10THENBargain

WHENpriceBETWEEN10and20THENAverage

ELSEGifttoimpressrelatives

END

FROMtitles

ORDERBYprice

GO

groupby中使用casewhen

上面的例子是CASE的典型用法,但是使用CASE其实可以做更多的事情。

比方说下面的GROUPBY子句中的CASE:

SELECTNumberofTitles,Count(*)

FROMtitles

GROUPBY

CASE

WHENpriceISNULLTHENUnpriced

WHENprice10THENBargain

WHENpriceBETWEEN10and20THENAverage

ELSEGifttoimpressrelatives

END

GO

组合orderby使用casewhen

你甚至还可以组合这些选项,添加一个ORDERBY子句,如下所示:

USEpubs

GO

SELECT

CASE

WHENpriceISNULLTHENUnpriced

WHENprice10THENBargain

WHENpriceBETWEEN10and20THENAverage

ELSEGifttoimpressrelatives

ENDASRange,

Title

FROMtitles

GROUPBY

CASE

WHENpriceISNULLTHENUnpriced

WHENprice10THENBargain

WHENpriceBETWEEN10and20THENAverage

ELSEGifttoimpressrelatives

END,

Title

ORDERBY

CASE

WHENpriceISNULLTHENUnpriced

WHENprice10THENBargain

WHENpriceBETWEEN10and20THENAverage

ELSEGifttoimpressrelatives

END,

Title

GO

注意,为了在GROUPBY块中使用CASE,查询语句需要在GROUPBY块中重复SELECT块中的CASE块。

除了选择自定义字段之外,在很多情况下CASE都非常有用。

再深入一步,你还可以得到你以前认为不可能得到的分组排序结果集。

替代union的casewhen

SELECTcountry,

SUM(CASEWHENsex=1THEN

populationELSE0END),–男性人口

SUM(CASEWHENsex=2THEN

populationELSE0END)–女性人口

FROMTable_A

GROUPBYcountry;

在Check中使用Case函数

在Check中使用Case函数在很多情况下都是非常不错的解决方法。

可能有很多人根本就不用Check,那么我建议你在看过下面的例子之后也尝试一下在SQL中使用Check。

下面我们来举个例子

公司A,这个公司有个规定,女职员的工资必须高于1000。

如果用Check和Case来表现的话,如下所示

CONSTRAINTcheck_salaryCHECK

(CASEWHENsex=2

THENCASEWHENsalary1000

THEN1ELSE0END

ELSE1END=1)

如果单纯使用Check,如下所示

CONSTRAINTcheck_salaryCHECK

(sex=2ANDsalary1000)

女职员的条件倒是符合了,男职员就无法输入了。

根据条件有选择的UPDATE

例,有如下更新条件

工资5000以上的职员,工资减少10%

工资在2000到4600之间的职员,工资增加15%

很容易考虑的是选择执行两次UPDATE语句,如下所示

–条件

UPDATEPersonnel

SETsalary=salary*0。

9

WHEREsalary=5000;

–条件

UPDATEPersonnel

SETsalary=salary*1。

15

WHEREsalary=2000ANDsalary4600;

但是事情没有想象得那么简单,假设有个人工资5000块。

首先,按照条件,工资减少10%,变成工资。

接下来运行第二个SQL时候,因为这个人的工资是在到的范围之内,需增加15%,最后这个人的工资结果是,不但没有减少,反而增加了。

如果要是反过来执行,那么工资的人相反会变成减少工资。

暂且不管这个规章是多么荒诞,如果想要一个SQL语句实现这个功能的话,我们需要用到Case函数。

代码如下:

CASEWHEN搜索函數

UPDATEPersonnel

SETsalary=CASEWHENsalary=5000

THENsalary*0。

9

WHENsalary=2000ANDsalary4600

THENsalary*1。

15

ELSEsalaryEND;

这里要注意一点,最后一行的ELSEsalary是必需的,要是没有这行,不符合这两个条件的人的工资将会被写成NUll,那可就大事不妙了。

在Case函数中Else部分的默认值是NULL,这点是需要注意的地方。

这种方法还可以在很多地方使用,比如说变更主键这种累活。

一般情况下,要想把两条数据的Primarykey,a和b交换,需要经过临时存储,拷贝,读回数据的三个过程,要是使用Case函数的话,一切都变得简单多了。

p_keycol_1col_2

a1张三

b2李四

c3王五

假设有如上数据,需要把主键a和b相互交换。

用Case函数来实现的话,代码如下

UPDATESomeTable

SETp_key=CASEWHENp_key=a

THENb

WHENp_key=b

THENa

ELSEp_keyEND

WHEREp_keyIN(a,b

同样的也可以交换两个Uniquekey。

需要注意的是,如果有需要交换主键的情况发生,多半是当初对这个表的设计进行得不够到位,建议检查表的设计是否妥当。

两个表数据是否一致的检查

Case函数不同于DECODE函数。

在Case函数中,可以使用BETWEEN,LIKE,ISNULL,IN,EXISTS等等。

比如说使用IN,EXISTS,可以进行子查询,从而实现更多的功能。

下面具个例子来说明,有两个表,tbl_A,tbl_B,两个表中都有keyCol列。

现在我们对两个表进行比较,tbl_A中的keyCol列的数据如果在tbl_B的keyCol列的数据中可以找到,返回结果Matched,如果没有找到,返回结果Unmatched。

要实现下面这个功能,可以使用下面两条语句

–使用IN的时候

SELECTkeyCol,

CASEWHENkeyColIN(SELECTkeyColFROMtbl_B)

THENMatched

ELSEUnmatchedENDLabel

FROMtbl_A;

–使用EXISTS的时候

SELECTkeyCol,

CASEWHENEXISTS(SELECT*FROMtbl_B

WHEREtbl_A。

keyCol=tbl_B。

keyCol)

THENMatched

ELSEUnmatchedENDLabel

FROMtbl_A;

使用IN和EXISTS的结果是相同的。

也可以使用NOTIN和NOTEXISTS,但是这个时候要注意NULL的情况。

在Case函数中使用合计函数

假设有下面一个表

学号(std_id)课程ID(class_id)课程名(class_name)主修flag(main_class_flg)

1001经济学Y

1002历史学N

2002历史学N

2003考古学Y

2004计算机N

3004计算机N

4005化学N

5006数学N

有的学生选择了同时修几门课程(100,200)也有的学生只选择了一门课程(300,400,500)。

选修多门课程的学生,要选择一门课程作为主修,主修flag里面写入Y。

只选择一门课程的学生,主修flag为N(实际上要是写入Y的话,就没有下面的麻烦事了,为了举例子,还请多多包含)。

现在我们要按照下面两个条件对这个表进行查询

只选修一门课程的人,返回那门课程的ID

选修多门课程的人,返回所选的主课程ID

简单的想法就是,执行两条不同的SQL语句进行查询。

条件

条件:只选择了一门课程的学生

SELECTstd_id,MAX(class_id)ASmain_class

FROMStudentclass

GROUPBYstd_id

HAVINGCOUNT(*)=1;

执行结果

STD_IDMAIN_class

3004

4005

5006

条件

条件:选择多门课程的学生

SELECTstd_id,class_idASmain_class

FROMStudentclass

WHEREmain_class_flg=Y

执行结果

STD_IDMAIN_class

1001

2003

如果使用Case函数,我们只要一条SQL语句就可以解决问题,具体如下所示

SELECTstd_id,

CASEWHENCOUNT(*)=1–只选择一门课程的学生的情况

THENMAX(class_id)

ELSEMAX(CASEWHENmain_class_flg=Y

THENclass_id

ELSENULLEND

ENDASmain_class

FROMStudentclass

GROUPBYstd_id;

运行结果

STD_IDMAIN_class

1001

2003

3004

4005

5006

通过在Case函数中嵌套Case函数,在合计函数中使用Case函数等方法,我们可以轻松的解决这个问题。

使用Case函数给我们带来了更大的自由度。

最后提醒一下使用Case函数的新手注意不要犯下面的错误

CASEcol_1

WHEN1THENRight

WHENNULLTHENWrong

END

在这个语句中WhenNull这一行总是返回unknown,所以永远不会出现Wrong的情况。

因为这句可以替换成WHENcol_1=NULL,这是一个错误的用法,这个时候我们应该选择用WHENcol_1ISNULL。

case行转列

数据库原来的数据:

修改后的数据:

selectballas球,sum(casetypewhen红thennumelse0end)as红,

sum(casetypewhen蓝thennumelse0end)as蓝,

sum(casetypewhen绿thennumelse0end)as绿

fromT_ballgroupbyball;

如下图

范围分组查询(groupby,case)

需求:查询在指定年龄段的人数

selectcasewhenage1andage26then1

whenage25andage27then2

else0endasstage,count(*)aspopulationfromstudent

groupbycasewhenage1andage26then1

whenage25andage27then2

else0end

结果

stagepopulation

112

225

可通过stage的值区分该条记录是属于哪个年龄段

未经允许不得转载:建站笔记[SqlServer]sql编程6-casewhen的使用

分享到:更多()

标签:SqlServercasewhen代码示例SqlServercasewhen方法的使用SqlServercasewhen深入理解

上一篇:[SqlServer]sql编程5-截取字符串常用函数