[SQL筆記] Case End 條件查詢二

R

• getdate()當前時間。select getdate()
• year(getdate())當前年份
• month(欄位名)取月。month(Birth) = 7
• day(欄位名)取日。day(Birth) >= 22
• case end類似 switch case

查詢

• year(getdate())當前年份
• year(欄位名)取年。 where year(EmployeeBirth) between 1980 and 1989
• month(欄位名)取月。 month(EmployeeBirth) = 6
• day(欄位名) 取日。 day(EmployeeBirth) >= 22

--查詢出80後的員工資料
select * from Employee where EmployeeBirth >= '1980-1-1' and EmployeeBirth <= '1989-12-31'
select * from Employee where EmployeeBirth between '1980-1-1' and '1989-12-31'
select * from Employee where year(EmployeeBirth) between 1980 and 1989
--查詢30-40歲之間，並且工資在40000-50000之間的員工資料
--年齡=當前年份-生日年份
select * from Employee where (year(getdate())-year(EmployeeBirth) >= 30 and  year(getdate())-year(EmployeeBirth)<=45)
and (EmployeeSalary >= 40000 and EmployeeSalary <= 50000)
--使用between and
select * from Employee 
where (year(getdate())-year(EmployeeBirth) between 30 and 45)
and (EmployeeSalary between 40000 and 50000)

--查詢出星座是巨蟹的員工資料(6.22-7.22)
select * from Employee where
(month(EmployeeBirth)=6 and day(EmployeeBirth) >= 22)
or
(month(EmployeeBirth)=7 and day(EmployeeBirth) <= 22)

子查詢

--查詢出工資比張三高的員工資料
select * from Employee where EmployeeSalary > 
(select EmployeeSalary from Employee where EmployeeName = '張三')
--查詢出和張三同一個城市的員工資料
select * from Employee where EmployeeAddress =
(select EmployeeAddress from Employee where EmployeeName='張三')
--查詢出生肖是鼠的員工資料(mod取12餘數)
--鼠、牛、虎、兔、龍、蛇、馬、羊、猴、雞、狗、豬
--4  5  6   7   8  9  10  11  0  1  2  3 (餘數)
select * from Employee where year(EmployeeBirth)%12 = 4

case end

類似switch case

語法：

select *,
case
    when 條件 then 條件成立時，要做的事
    else 條件不成立時，要做的事
end 欄位別名
from Employee

--查詢出生肖是鼠的員工資料(mod取12餘數)
--鼠、牛、虎、兔、龍、蛇、馬、羊、猴、雞、狗、豬
--4  5  6   7   8  9  10  11  0  1  2  3 (餘數)
select * from Employee where year(EmployeeBirth)%12 = 4
-- 查詢所有員工資料，新增一列，顯示生肖
select *,
case
  when year(EmployeeBirth) % 10 = 4 then '鼠'
  when year(EmployeeBirth) % 12 = 5 then '牛'
  when year(EmployeeBirth) % 12 = 6 then '虎'
  when year(EmployeeBirth) % 12 = 7 then '兔'
  when year(EmployeeBirth) % 12 = 8 then '龍'
  when year(EmployeeBirth) % 12 = 9 then '蛇'
  when year(EmployeeBirth) % 12 = 10 then '馬'
  when year(EmployeeBirth) % 12 = 11 then '羊'
  when year(EmployeeBirth) % 12 = 0 then '猴'
  when year(EmployeeBirth) % 12 = 1 then '狗'
  when year(EmployeeBirth) % 12 = 2 then '豬'
  else '' --以上都不成立，顯示空字串
end 生肖
from Employee

可以把它想成這段做變化

select *, 
 EmployeeSalary+100 調薪後 --這段做變化，換成case end
from Employee
--
select *, 
  case
    -- TODO
  end
from Employee

可以簡寫成：

case 條件(表達式)
    when 條件結果相等 then 成立要做的事
end

select *,
case year(EmployeeBirth) % 12 
  when 4 then '鼠'
  when 5 then '牛'
  when 6 then '虎'
  when 7 then '兔'
  when 8 then '龍'
  when 9 then '蛇'
  when 10 then '馬'
  when 11 then '羊'
  when 0 then '猴'
  when 1 then '狗'
  when 2 then '豬'
  else '' --以上都不成立
end 生肖
from Employee

• CASE (Transact-SQL)
• https://www.bilibili.com/video/BV1XV411C7TP?p=9