手机网站dedecms,成都建立网站营销设计,怎么做五合一网站,网站建设学院知识重点#xff1a;1.extract(day from schedule01::timestamp)13Extract 属于 SQL 的 DML(即数据库管理语言)函数#xff0c;同样#xff0c;InterBase 也支持 Extract#xff0c;它主要用于从一个日期或时间型的字段内抽取年、月、日、时、分、秒数据#xff0c;因此1.extract(day from schedule01::timestamp)13Extract 属于 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_schedule23 (45 id integer NOT NULL DEFAULT nextval(temp_schedule_id_seq::regclass),67 schedule01 timestamp without time zone,--日期89 schedule03 character varying(255),--姓名1011 state character varying(255),--值班状态(0休 1班)1213 CONSTRAINT temp_schedule_pkey PRIMARY KEY (id)1415 )View Code1.查询SQL语句1 select schedule03,schedule01,state fromtemp_schedule23 where extract(year from schedule01)2017 and extract(month from schedule01)645 order by schedule03,schedule01;View Code显示为2.现在需要根据(6月的)日期从1号开始根据人员名称横向合并排列数据(即只显示两行)显示效果如下实现的SQL语句如下1 select schedule03 asname23 ,max(case when extract(day from schedule01::timestamp)1 then state end) asday145 ,max(case when extract(day from schedule01::timestamp)2 then state end) asday267 ,max(case when extract(day from schedule01::timestamp)3 then state end) asday389 ,max(case when extract(day from schedule01::timestamp)4 then state end) asday41011 ,max(case when extract(day from schedule01::timestamp)5 then state end) asday51213 ,max(case when extract(day from schedule01::timestamp)6 then state end) asday61415 ,max(case when extract(day from schedule01::timestamp)7 then state end) asday71617 ,max(case when extract(day from schedule01::timestamp)8 then state end) asday81819 ,max(case when extract(day from schedule01::timestamp)9 then state end) asday92021 ,max(case when extract(day from schedule01::timestamp)10 then state end) asday102223 ,max(case when extract(day from schedule01::timestamp)11 then state end) asday112425 ,max(case when extract(day from schedule01::timestamp)12 then state end) asday122627 ,max(case when extract(day from schedule01::timestamp)13 then state end) asday132829 fromtemp_schedule3031 where extract(year from schedule01)2017 and extract(month from schedule01)63233 group by schedule03;View Code3.将人员的值班状态通过汉字(0休 1班)显示出来显示效果如下SQL语句(主要是实现case的嵌套)1 select schedule03 asname23 ,max(case when extract(day from schedule01::timestamp)1 then (case when state0 then 休 else 班 end) end) asday145 ,max(case when extract(day from schedule01::timestamp)2 then (case when state0 then 休 else 班 end) end) asday267 ,max(case when extract(day from schedule01::timestamp)3 then (case when state0 then 休 else 班 end) end) asday389 ,max(case when extract(day from schedule01::timestamp)4 then (case when state0 then 休 else 班 end) end) asday41011 ,max(case when extract(day from schedule01::timestamp)5 then (case when state0 then 休 else 班 end) end) asday51213 ,max(case when extract(day from schedule01::timestamp)6 then (case when state0 then 休 else 班 end) end) asday61415 ,max(case when extract(day from schedule01::timestamp)7 then (case when state0 then 休 else 班 end) end) asday71617 ,max(case when extract(day from schedule01::timestamp)8 then (case when state0 then 休 else 班 end) end) asday81819 ,max(case when extract(day from schedule01::timestamp)9 then (case when state0 then 休 else 班 end) end) asday92021 ,max(case when extract(day from schedule01::timestamp)10 then (case when state0 then 休 else 班 end) end) asday102223 ,max(case when extract(day from schedule01::timestamp)11 then (case when state0 then 休 else 班 end) end) asday112425 ,max(case when extract(day from schedule01::timestamp)12 then (case when state0 then 休 else 班 end) end) asday122627 ,max(case when extract(day from schedule01::timestamp)13 then (case when state0 then 休 else 班 end) end) asday132829 fromtemp_schedule3031 where extract(year from schedule01)2017 and extract(month from schedule01)63233 group by schedule03 ;View Code知识一点点的累积技术一点点的提高加油
