知識重點：

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月的值班信息

表結構如下：

1 CREATE TABLE public.temp_schedule2

3 (4

5 id integer NOT NULL DEFAULT nextval('temp_schedule_id_seq'::regclass),6

7 schedule01 timestamp without time zone,--日期8

9 schedule03 character varying(255),--姓名10

11 state character varying(255),--值班狀態(0休 1班)12

13 CONSTRAINT temp_schedule_pkey PRIMARY KEY (id)14

15 )

View Code

1.查詢SQL語句：

1 select schedule03,schedule01,state fromtemp_schedule2

3 where extract(year from schedule01)=2017 and extract(month from schedule01)=6

4

5 order by schedule03,schedule01;

View Code

顯示為：

2.現在需要根據(6月的)日期，從1號開始根據人員名稱橫向合并排列數據(即只顯示兩行)

顯示效果如下：

實現的SQL語句如下：

1 select schedule03 asname2

3 ,max(case when extract(day from schedule01::timestamp)=1 then state end) asday14

5 ,max(case when extract(day from schedule01::timestamp)=2 then state end) asday26

7 ,max(case when extract(day from schedule01::timestamp)=3 then state end) asday38

9 ,max(case when extract(day from schedule01::timestamp)=4 then state end) asday410

11 ,max(case when extract(day from schedule01::timestamp)=5 then state end) asday512

13 ,max(case when extract(day from schedule01::timestamp)=6 then state end) asday614

15 ,max(case when extract(day from schedule01::timestamp)=7 then state end) asday716

17 ,max(case when extract(day from schedule01::timestamp)=8 then state end) asday818

19 ,max(case when extract(day from schedule01::timestamp)=9 then state end) asday920

21 ,max(case when extract(day from schedule01::timestamp)=10 then state end) asday1022

23 ,max(case when extract(day from schedule01::timestamp)=11 then state end) asday1124

25 ,max(case when extract(day from schedule01::timestamp)=12 then state end) asday1226

27 ,max(case when extract(day from schedule01::timestamp)=13 then state end) asday1328

29 fromtemp_schedule30

31 where extract(year from schedule01)=2017 and extract(month from schedule01)=6

32

33 group by schedule03;

View Code

3.將人員的值班狀態通過漢字(0休 1班)顯示出來，顯示效果如下：

SQL語句(主要是實現case的嵌套)：

1 select schedule03 asname2

3 ,max(case when extract(day from schedule01::timestamp)=1 then (case when state='0' then '休' else '班' end) end) asday14

5 ,max(case when extract(day from schedule01::timestamp)=2 then (case when state='0' then '休' else '班' end) end) asday26

7 ,max(case when extract(day from schedule01::timestamp)=3 then (case when state='0' then '休' else '班' end) end) asday38

9 ,max(case when extract(day from schedule01::timestamp)=4 then (case when state='0' then '休' else '班' end) end) asday410

11 ,max(case when extract(day from schedule01::timestamp)=5 then (case when state='0' then '休' else '班' end) end) asday512

13 ,max(case when extract(day from schedule01::timestamp)=6 then (case when state='0' then '休' else '班' end) end) asday614

15 ,max(case when extract(day from schedule01::timestamp)=7 then (case when state='0' then '休' else '班' end) end) asday716

17 ,max(case when extract(day from schedule01::timestamp)=8 then (case when state='0' then '休' else '班' end) end) asday818

19 ,max(case when extract(day from schedule01::timestamp)=9 then (case when state='0' then '休' else '班' end) end) asday920

21 ,max(case when extract(day from schedule01::timestamp)=10 then (case when state='0' then '休' else '班' end) end) asday1022

23 ,max(case when extract(day from schedule01::timestamp)=11 then (case when state='0' then '休' else '班' end) end) asday1124

25 ,max(case when extract(day from schedule01::timestamp)=12 then (case when state='0' then '休' else '班' end) end) asday1226

27 ,max(case when extract(day from schedule01::timestamp)=13 then (case when state='0' then '休' else '班' end) end) asday1328

29 fromtemp_schedule30

31 where extract(year from schedule01)=2017 and extract(month from schedule01)=6

32

33 group by schedule03 ;

View Code

知識一點點的累積，技術一點點的提高！加油！

總結

以上是生活随笔為你收集整理的mysql 坚向变横向_（排班表一）使用SQL语句使数据从坚向排列转化成横向排列的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。