case運算式---多條件運算式

主題：case運算式—多條件運算式

一、概念

1、基本語法

case when 條件1  then 值1
	when 條件2  then 值2
	when 條件3  then 值3
	....
	else 值n  end

2、執行程序：

整個運算式有一個計算結果：和if else if else 一樣

3、語法要求

1）條件是有順序的

2）所有的條件后面的值，要是同一型別

3）建議不要少了else，如果省了，則所有when都不成立，則回傳null

4）end不能少

5）每一個條件后不要打,

二、替換應用

select st_id,(
  CASE WHEN course_id='001' THEN 'java'
      WHEN  course_id='002' THEN 'oracle'
      WHEN  course_id='003' THEN 'python'
      ELSE null END
) as kcm,score
from cjb;

執行流程：

1）從from后面的表中取第一行，用這一行作為引數，執行select

2）select從左至右一個一個列得出結果，作為查詢結果第一行的值

3）再取from后面表的下一行，執行上面兩步，直到表中所有行

三、行列轉換

表的豎橫轉換

注意：表查詢處理單位是from后面表的行

select st_id,
       (case when course_id='001' then score else 0 end) as java,
       (case when course_id='002' then score else 0 end) as oracle,
       (case when course_id='003' then score else 0 end) as python
from cjb
group by st_id

執行流程：

1）選擇from后面的表中第一行

2）用第一行的各列的值為引數，代入到select后面的各列的運算式中，計算結果，形成結果的第一行

3）用from后面的表中的下一行，執行12兩步，直到from后面表中所有行處理完

select st_id,
       sum(case when course_id='001' then score else 0 end) as java,
       sum(case when course_id='002' then score else 0 end) as oracle,
       sum(case when course_id='003' then score else 0 end) as python
from cjb
group by st_id

練習：查詢成績表，顯示為如下結果

學號 java oracle python

010103 已選修 已選修 未選修

select st_id,(case when st_id in(select st_id from cjb where course_id='001') then '已選修' else '未選修' end)as java,
(case when st_id in(select st_id from cjb where course_id='002') then '已選修' else '未選修' end)as oracle,
(case when st_id in(select st_id from cjb where course_id='003') then '已選修' else '未選修' end)as 離散數學
from xsb;

四、多條件約束

為cjb添加成績約束：001課程【0，100】，002課程【0，120】，003課程【0，150】

方法一：

alter table cjb
    add(constraint ck_score check((case when course_id='001' and score>=0 and score<=100 then 1
                                       when course_id='002' and score>=0 and score<=120 then 1
                                       when course_id='003' and score>=0 and score<=150 then 1
                                       else 0 end)=1 ))                      

方法二：

alter table cjb
    add(constraint ck_score check(case when course_id='001' then case when score>=0 and score<=100 then 1 else 0 end
                                       when course_id='002' then case when score>=0 and score<=120 then 1 else 0 end
                                       when course_id='003' then case when score>=0 and score<=150 then 1 else 0 end
                                       else 1 end =1))

說明：

• check約束的本質是當往資料表中插入資料或更新資料時，執行check的約束陳述句，得出結果真偽，真就插入或插入成功，假就拒絕插入或更新

• case回傳的一個普通值，不是真偽

• case不是回傳一個運算式，而是回傳一個運算式的計算結果，這個結果是一個普通的值

• 最后的else的值為0和1的含義是不同的

• 方法1和方法2是有區別的，方法1的else不能回傳1

• 為了是的check中的回傳值是真偽，用case的結果和一個值比較

五、主鍵值交換

交換兩個主鍵的值，交換xsb中兩個學生的學號

select * from xsb
--方法一：
update xsb set st_id='a' where st_id='010110'
update xsb set st_id='010110' where st_id='010111'
update xsb set st_id='010111' where st_id='a'
---上面方法繁瑣，還效率低
--方法二：
update xsb 
set st_id=case when st_id='010110' then '010111' 
               when st_id='010111' then '010110'
               else st_id end
where st_id in ('010110','010111')

說明：

• 方法一：如果不用輪換，是會違反主鍵約束
• 方法二：由于在一次update執行程序中，主鍵可以相同，但是在執行完畢必須不同

六、多條件更新

修改cjb中的記錄的成績值，【70-80】之間變成1.1倍，如果是【80-90】之間變成原來的0.9
根據上面方法，完成