SQL中Case的妙用(三)

四，根据条件有选择的UPDATE。 

例，有如下更新条件 

1. 工资5000以上的职员，工资减少10% 

2. 工资在2000到4600之间的职员，工资增加15% 

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

 

--条件1

 

UPDATE Personnel

 

SET salary = salary * 0.9

 

WHERE salary >= 5000;

 

--条件2

 

UPDATE Personnel

 

SET salary = salary * 1.15

 

WHERE salary >= 2000 AND salary < 4600;

 

 

但是事情没有想象得那么简单，假设有个人工资5000块。首先，按照条件1，工资减少10%，变成工资4500。接下来运行第二个SQL时候，因为这个人的工资是4500在2000到4600的范围之内， 需增加15%，最后这个人的工资结果是5175,不但没有减少，反而增加了。如果要是反过来执行，那么工资4600的人相反会变成减少工资。暂且不管这个规章是多么荒诞，如果想要一个SQL 语句实现这个功能的话，我们需要用到Case函数。代码如下:

 

UPDATE Personnel

 

SET salary = CASE WHEN salary >= 5000

 

　            THEN salary * 0.9

 

WHEN salary >= 2000 AND salary < 4600

 

THEN salary * 1.15

 

ELSE salary END;

 

 

 

这里要注意一点，最后一行的ELSE salary是必需的，要是没有这行，不符合这两个条件的人的工资将会被写成NUll,那可就大事不妙了。在Case函数中Else部分的默认值是NULL，这点是需要注意的地方。 
这种方法还可以在很多地方使用，比如说变更主键这种累活。 
一般情况下，要想把两条数据的Primary key,a和b交换，需要经过临时存储，拷贝，读回数据的三个过程，要是使用Case函数的话，一切都变得简单多了。

 

p_key	col_1	col_2
a	1	张三
b	2	李四
c	3	王五

 

假设有如上数据，需要把主键a和b相互交换。用Case函数来实现的话，代码如下 

 

UPDATE SomeTable

 

SET p_key = CASE WHEN p_key = 'a'

 

THEN 'b'

 

WHEN p_key = 'b'

 

THEN 'a'

 

ELSE p_key END

 

WHERE p_key IN ('a', 'b');

 

 

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

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

Case函数不同于DECODE函数。在Case函数中，可以使用BETWEEN,LIKE,IS NULL,IN,EXISTS等等。比如说使用IN,EXISTS，可以进行子查询，从而 实现更多的功能。 
下面具个例子来说明，有两个表，tbl_A,tbl_B，两个表中都有keyCol列。现在我们对两个表进行比较，tbl_A中的keyCol列的数据如果在tbl_B的keyCol列的数据中可以找到， 返回结果'Matched',如果没有找到，返回结果'Unmatched'。 
要实现下面这个功能，可以使用下面两条语句 

 

--使用IN的时候

 

SELECT keyCol,

 

CASE WHEN keyCol IN ( SELECT keyCol FROM tbl_B )

 

THEN 'Matched'

 

ELSE 'Unmatched' END Label

 

FROM tbl_A;

 

--使用EXISTS的时候

 

SELECT keyCol,

 

CASE WHEN EXISTS ( SELECT * FROM tbl_B

 

WHERE tbl_A.keyCol = tbl_B.keyCol )

 

THEN 'Matched'

 

ELSE 'Unmatched' END Label

 

FROM tbl_A;

使用IN和EXISTS的结果是相同的。也可以使用NOT IN和NOT EXISTS，但是这个时候要注意NULL的情况。