知識重點： 1.extract(day from schedule01::timestamp)=13 Extract 屬於 SQL 的 DML（即資料庫管理語言）函數，同樣，InterBase 也支持 Extract，它主要用於從一個日期或時間型的欄位內抽取年、月、日、時、分、秒數據，因此，它支持其 ...
知識重點：
1.extract(day from schedule01::timestamp)=13
Extract 屬於 SQL 的 DML（即資料庫管理語言）函數，同樣，InterBase 也支持 Extract，它主要用於從一個日期或時間型的欄位內抽取年、月、日、時、分、秒數據，因此，它支持其關健字 YEAR、MONTH、DAY、HOUR、MINUTE、SECOND、WEEKDAY、YEARDAY。
Extract 的使用語法為：
EXTRACT（關健字 FROM 日期或時間型欄位）
如：extract(year from schedule01)=2017從日期中提取年份
2.max()函數：取最大值
3.case()函數的嵌套
註意嵌套case()函數時，每個case的開始和結束。
排班功能：現有兩個人員(A和B)，他們在不同日期的值班狀態(state)不同，現在要查詢他們在2017.6月的值班信息
表結構如下：
CREATE TABLE public.temp_schedule
(
id integer NOT NULL DEFAULT nextval('temp_schedule_id_seq'::regclass),
schedule01 timestamp without time zone,--日期
schedule03 character varying(255),--姓名
state character varying(255),--值班狀態(0休 1班)
CONSTRAINT temp_schedule_pkey PRIMARY KEY (id)
)
1.查詢SQL語句：
select schedule03,schedule01,state from temp_schedule
where extract(year from schedule01)=2017 and extract(month from schedule01)=6
order by schedule03,schedule01;
顯示為：
2.現在需要根據(6月的)日期，從1號開始根據人員名稱橫向合併排列數據(即只顯示兩行)
顯示效果如下：
實現的SQL語句如下：
select schedule03 as name
,max(case when extract(day from schedule01::timestamp)=1 then state end) as day1
,max(case when extract(day from schedule01::timestamp)=2 then state end) as day2
,max(case when extract(day from schedule01::timestamp)=3 then state end) as day3
,max(case when extract(day from schedule01::timestamp)=4 then state end) as day4
,max(case when extract(day from schedule01::timestamp)=5 then state end) as day5
,max(case when extract(day from schedule01::timestamp)=6 then state end) as day6
,max(case when extract(day from schedule01::timestamp)=7 then state end) as day7
,max(case when extract(day from schedule01::timestamp)=8 then state end) as day8
,max(case when extract(day from schedule01::timestamp)=9 then state end) as day9
,max(case when extract(day from schedule01::timestamp)=10 then state end) as day10
,max(case when extract(day from schedule01::timestamp)=11 then state end) as day11
,max(case when extract(day from schedule01::timestamp)=12 then state end) as day12
,max(case when extract(day from schedule01::timestamp)=13 then state end) as day13
from temp_schedule
where extract(year from schedule01)=2017 and extract(month from schedule01)=6
group by schedule03;
3.將人員的值班狀態通過漢字(0休 1班)顯示出來，顯示效果如下：
SQL語句(主要是實現case的嵌套)：
select schedule03 as name
,max(case when extract(day from schedule01::timestamp)=1 then (case when state='0' then '休' else '班' end) end) as day1
,max(case when extract(day from schedule01::timestamp)=2 then (case when state='0' then '休' else '班' end) end) as day2
,max(case when extract(day from schedule01::timestamp)=3 then (case when state='0' then '休' else '班' end) end) as day3
,max(case when extract(day from schedule01::timestamp)=4 then (case when state='0' then '休' else '班' end) end) as day4
,max(case when extract(day from schedule01::timestamp)=5 then (case when state='0' then '休' else '班' end) end) as day5
,max(case when extract(day from schedule01::timestamp)=6 then (case when state='0' then '休' else '班' end) end) as day6
,max(case when extract(day from schedule01::timestamp)=7 then (case when state='0' then '休' else '班' end) end) as day7
,max(case when extract(day from schedule01::timestamp)=8 then (case when state='0' then '休' else '班' end) end) as day8
,max(case when extract(day from schedule01::timestamp)=9 then (case when state='0' then '休' else '班' end) end) as day9
,max(case when extract(day from schedule01::timestamp)=10 then (case when state='0' then '休' else '班' end) end) as day10
,max(case when extract(day from schedule01::timestamp)=11 then (case when state='0' then '休' else '班' end) end) as day11
,max(case when extract(day from schedule01::timestamp)=12 then (case when state='0' then '休' else '班' end) end) as day12
,max(case when extract(day from schedule01::timestamp)=13 then (case when state='0' then '休' else '班' end) end) as day13
from temp_schedule
where extract(year from schedule01)=2017 and extract(month from schedule01)=6
group by schedule03 ;
知識一點點的累積，技術一點點的提高！加油！
