使用SQL语句使数据从坚向排列转化成横向排列(排…

2018-06-17 19:55:41来源:未知 阅读 ()

新老客户大回馈,云服务器低至5折

知识重点:

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_schedule
 2 
 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  from temp_schedule
2 
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 as name
 2 
 3 ,max(case when extract(day from schedule01::timestamp)=1 then state end) as day1
 4 
 5 ,max(case when extract(day from schedule01::timestamp)=2 then state end) as day2
 6 
 7 ,max(case when extract(day from schedule01::timestamp)=3 then state end) as day3
 8 
 9 ,max(case when extract(day from schedule01::timestamp)=4 then state end) as day4
10 
11 ,max(case when extract(day from schedule01::timestamp)=5 then state end) as day5
12 
13 ,max(case when extract(day from schedule01::timestamp)=6 then state end) as day6
14 
15 ,max(case when extract(day from schedule01::timestamp)=7 then state end) as day7
16 
17 ,max(case when extract(day from schedule01::timestamp)=8 then state end) as day8
18 
19 ,max(case when extract(day from schedule01::timestamp)=9 then state end) as day9
20 
21 ,max(case when extract(day from schedule01::timestamp)=10 then state end) as day10
22 
23 ,max(case when extract(day from schedule01::timestamp)=11 then state end) as day11
24 
25 ,max(case when extract(day from schedule01::timestamp)=12 then state end) as day12
26 
27 ,max(case when extract(day from schedule01::timestamp)=13 then state end) as day13
28 
29 from temp_schedule
30 
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 as name
 2 
 3 ,max(case when extract(day from schedule01::timestamp)=1 then (case when state='0' then '' else '' end) end) as day1
 4 
 5 ,max(case when extract(day from schedule01::timestamp)=2 then (case when state='0' then '' else '' end) end) as day2
 6 
 7 ,max(case when extract(day from schedule01::timestamp)=3 then (case when state='0' then '' else '' end) end) as day3
 8 
 9 ,max(case when extract(day from schedule01::timestamp)=4 then (case when state='0' then '' else '' end) end) as day4
10 
11 ,max(case when extract(day from schedule01::timestamp)=5 then (case when state='0' then '' else '' end) end) as day5
12 
13 ,max(case when extract(day from schedule01::timestamp)=6 then (case when state='0' then '' else '' end) end) as day6
14 
15 ,max(case when extract(day from schedule01::timestamp)=7 then (case when state='0' then '' else '' end) end) as day7
16 
17 ,max(case when extract(day from schedule01::timestamp)=8 then (case when state='0' then '' else '' end) end) as day8
18 
19 ,max(case when extract(day from schedule01::timestamp)=9 then (case when state='0' then '' else '' end) end) as day9
20 
21 ,max(case when extract(day from schedule01::timestamp)=10 then (case when state='0' then '' else '' end) end) as day10
22 
23 ,max(case when extract(day from schedule01::timestamp)=11 then (case when state='0' then '' else '' end) end) as day11
24 
25 ,max(case when extract(day from schedule01::timestamp)=12 then (case when state='0' then '' else '' end) end) as day12
26 
27 ,max(case when extract(day from schedule01::timestamp)=13 then (case when state='0' then '' else '' end) end) as day13
28 
29 from temp_schedule
30 
31 where extract(year from schedule01)=2017 and extract(month from schedule01)=6
32 
33 group by schedule03 ;
View Code

 


 

  知识一点点的累积,技术一点点的提高!加油!

 

标签:

版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com
特别注意:本站所有转载文章言论不代表本站观点,本站所提供的摄影照片,插画,设计作品,如需使用,请与原作者联系,版权归原作者所有

上一篇:常用数据库 JDBC URL 格式

下一篇:PostgreSQL常用函数