使用SQL语句使数据从坚向排列转化成横向排列(排班表)
知识重点：
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 ;
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
知识一点点的累积，技术一点点的提高！加油！
最新文章
- 输入任意一个字符串，如：“abDEe23dJfd343dPOddfe4CdD5ccv!23rr”。取出该字符串中所有的字母。顺序不能改变！并把大写字母变成小写，小写字母变成大写！
- EntityFramework 启用迁移 Enable-Migrations 报异常 "No context type was found in the assembly"
- 算法手记 之 数据结构(并查集详解)(POJ1703)
- java文件末尾追加内容的两种方式
- 转：redis常用命令
- 关于java“配置环境变量”的那些事
- Android实现传感器应用及位置服务
- IP访问SQL数据库设置
- 转载 C#匿名函数 委托和Lambda表达式
- Gridview将某列内容和标题隐藏起来
- 纳税服务系统【用户模块之使用POI导入excel、导出excel】
- The POM for ... is missing, no dependency information available
- android：layout_weight属性详解
- Nginx安装及配置
- 新更新，又是一年了。这次记录下关于android版的WeiboDemo的问题
- SSH上传/下载本地文件到linux服务器
- Linux服务器SSH免密互访
- Linux中inotify软件部署及参数事件演示
- 从0开始搭建vue+webpack脚手架（四）
- OpenVAS应用实例