如何编写存储过程将刷卡记录brushCard排列到arrange表？

Question

现在数据库有表brushCard，保存刷卡机传到数据库的数据。brushCard表有4个字段，empNo(员工编号)，empName(姓名),bcDate(员工刷卡日期)，bcTime(员工刷卡时间).empNo是int数据类型,empName是char数据类型,bcDate，bcTime都是DateTime数据类型
brushCard表：empNo        empName        bcDate                         bcTime
6            李四           2010-01-13 00:00:00.000        1900-01-01 07:45:06.000
6            李四           2010-01-13 00:00:00.000        1900-01-01 12:25:02.000
6            李四           2010-01-13 00:00:00.000        1900-01-01 17:35:26.000
6            李四           2010-01-13 00:00:00.000        1900-01-01 21:05:36.000  
7            王五           2010-01-13 00:00:00.000        1900-01-01 07:55:36.000
7            王五           2010-01-13 00:00:00.000        1900-01-01 12:05:36.000
7            王五           2010-01-13 00:00:00.000        1900-01-01 17:34:36.000
7            王五           2010-01-13 00:00:00.000        1900-01-01 21:27:36.000
8            赵六           2010-01-13 00:00:00.000        1900-01-01 07:47:23.000  
8            赵六           2010-01-13 00:00:00.000        1900-01-01 12:37:23.000  
8            赵六           2010-01-13 00:00:00.000        1900-01-01 17:07:23.000  
8            赵六           2010-01-13 00:00:00.000        1900-01-01 21:37:23.000  现在新建arrange表:empNo   empName  bcDate         bcTime1         bcTime2         bcTime3         bcTime3bcTime1字段:1900-01-01 07:00:00.000~1900-01-01 09:00:00.000
bcTime2字段:1900-01-01 12:00:00.000~1900-01-01 14:00:00.000
bcTime3字段:1900-01-01 17:00:00.000~1900-01-01 19:00:00.000
bcTime4字段:1900-01-01 20:00:00.000~1900-01-01 22:00:00.000
问题：如何创建一个存储过程？可以将brushCard记录排列到arrange里面
类似于下面判断，将brushCard数据排列到arrange中？
如果bctime between  1900-01-01 07:00:00.000~1900-01-01 09:00:00.000 then bcTime1=bctime
如果bctime between  1900-01-01 12:00:00.000~1900-01-01 14:00:00.000 then bcTime2=bctime
如果bctime between  1900-01-01 17:00:00.000~1900-01-01 19:00:00.000 then bcTime3=bctime
如果bctime between  1900-01-01 20:00:00.000~1900-01-01 22:00:00.000 then bcTime4=bctime
 

Answer 1

--> 测试数据: [brushCard]
if object_id('[brushCard]') is not null drop table [brushCard]
create table [brushCard] (empNo int,empName varchar(4),bcDate datetime,bcTime datetime)
insert into [brushCard]
select 6,'李四','2010-01-13 00:00:00.000','1900-01-01 07:45:06.000' union all
select 6,'李四','2010-01-13 00:00:00.000','1900-01-01 12:25:02.000' union all
select 6,'李四','2010-01-13 00:00:00.000','1900-01-01 17:35:26.000' union all
select 6,'李四','2010-01-13 00:00:00.000','1900-01-01 21:05:36.000' union all
select 7,'王五','2010-01-13 00:00:00.000','1900-01-01 07:55:36.000' union all
select 7,'王五','2010-01-13 00:00:00.000','1900-01-01 12:05:36.000' union all
select 7,'王五','2010-01-13 00:00:00.000','1900-01-01 17:34:36.000' union all
select 7,'王五','2010-01-13 00:00:00.000','1900-01-01 21:27:36.000' union all
select 8,'赵六','2010-01-13 00:00:00.000','1900-01-01 07:47:23.000' union all
select 8,'赵六','2010-01-13 00:00:00.000','1900-01-01 12:37:23.000' union all
select 8,'赵六','2010-01-13 00:00:00.000','1900-01-01 17:07:23.000' union all
select 8,'赵六','2010-01-13 00:00:00.000','1900-01-01 21:37:23.000'
--> 测试数据: [arrange]
if object_id('[arrange]') is not null drop table [arrange]
create table [arrange] (empNo int,empName varchar(4),bcDate datetime,bcTime1 datetime,bcTime2 datetime,bcTime3 datetime,bcTime4 datetime)
go
--插入
insert into  [arrange]
select empno,empname,bcdate,
bctime1=max(case when bctime between  '1900-01-01 07:00:00' and '1900-01-01 09:00:00' then bctime else null end),
bctime2=max(case when bctime between  '1900-01-01 12:00:00' and '1900-01-01 14:00:00' then bctime else null end),
bctime3=max(case when bctime between  '1900-01-01 17:00:00' and '1900-01-01 19:00:00' then bctime else null end),
bctime4=max(case when bctime between  '1900-01-01 20:00:00' and '1900-01-01 22:00:00' then bctime else null end)
from [brushCard]
group by empno,empname,bcdate

Answer 2

--结果：
empno       empname bcdate                  bctime1                 bctime2                 bctime3                 bctime4
----------- ------- ----------------------- ----------------------- ----------------------- ----------------------- -----------------------
6           李四      2010-01-13 00:00:00.000 1900-01-01 07:45:06.000 1900-01-01 12:25:02.000 1900-01-01 17:35:26.000 1900-01-01 21:05:36.000
7           王五      2010-01-13 00:00:00.000 1900-01-01 07:55:36.000 1900-01-01 12:05:36.000 1900-01-01 17:34:36.000 1900-01-01 21:27:36.000
8           赵六      2010-01-13 00:00:00.000 1900-01-01 07:47:23.000 1900-01-01 12:37:23.000 1900-01-01 17:07:23.000 1900-01-01 21:37:23.000

Answer 3

--> 测试数据: [brushCard]
if object_id('[brushCard]') is not null drop table [brushCard]
create table [brushCard] (empNo int,empName varchar(4),bcDate datetime,bcTime datetime)
insert into [brushCard]
select 6,'李四','2010-01-13 00:00:00.000','1900-01-01 07:45:06.000' union all
select 6,'李四','2010-01-13 00:00:00.000','1900-01-01 12:25:02.000' union all
select 6,'李四','2010-01-13 00:00:00.000','1900-01-01 17:35:26.000' union all
select 6,'李四','2010-01-13 00:00:00.000','1900-01-01 21:05:36.000' union all
select 7,'王五','2010-01-13 00:00:00.000','1900-01-01 07:55:36.000' union all
select 7,'王五','2010-01-13 00:00:00.000','1900-01-01 12:05:36.000' union all
select 7,'王五','2010-01-13 00:00:00.000','1900-01-01 17:34:36.000' union all
select 7,'王五','2010-01-13 00:00:00.000','1900-01-01 21:27:36.000' union all
select 8,'赵六','2010-01-13 00:00:00.000','1900-01-01 07:47:23.000' union all
select 8,'赵六','2010-01-13 00:00:00.000','1900-01-01 12:37:23.000' union all
select 8,'赵六','2010-01-13 00:00:00.000','1900-01-01 17:07:23.000' union all
select 8,'赵六','2010-01-13 00:00:00.000','1900-01-01 21:37:23.000'
--> 测试数据: [arrange]
if object_id('[arrange]') is not null drop table [arrange]
create table [arrange] (empNo int,empName varchar(4),bcDate datetime,bcTime1 datetime,bcTime2 datetime,bcTime3 datetime,bcTime4 datetime)insert into  [arrange]
select empno,empname,bcdate,
max(case when convert(varchar(8),bctime,108) between  '07:00:00' and '09:00:00' then bctime else null end),
max(case when convert(varchar(8),bctime,108) between  '12:00:00' and '14:00:00' then bctime else null end),
max(case when convert(varchar(8),bctime,108) between  '17:00:00' and '19:00:00' then bctime else null end),
max(case when convert(varchar(8),bctime,108) between  '20:00:00' and '22:00:00' then bctime else null end)
from [brushCard]
group by empno,empname,bcdateselect * from arrange
---------------------------
6 李四 2010-01-13 00:00:00.000 1900-01-01 07:45:06.000 1900-01-01 12:25:02.000 1900-01-01 17:35:26.000 1900-01-01 21:05:36.000
7 王五 2010-01-13 00:00:00.000 1900-01-01 07:55:36.000 1900-01-01 12:05:36.000 1900-01-01 17:34:36.000 1900-01-01 21:27:36.000
8 赵六 2010-01-13 00:00:00.000 1900-01-01 07:47:23.000 1900-01-01 12:37:23.000 1900-01-01 17:07:23.000 1900-01-01 21:37:23.000

Answer 4

--try
insert into arrange(empNo,empName,bcDate,bcTime1,bcTime2,bcTime3, bcTime3)select empNo,empName,bcDate,
bctime1=case when bctime between  '1900-01-01 07:00:00.000' and '1900-01-01 09:00:00.000' then bctim end,
bctime2=case when bctime between  '1900-01-01 12:00:00.000' and '1900-01-01 14:00:00.000' then bctim end,
bctime3=case when bctime between  '1900-01-01 17:00:00.000' and '1900-01-01 19:00:00.000' then bctim end,from brushCard

Answer 5

哥：为什么我出现
 警告: 聚合或其他 SET 操作消除了 Null 值。
(11 行受影响)6 张三 1900-01-01 07:45:06.000 1900-01-01 07:45:06.000 NULL NULL NULL
6 张三 1900-01-01 12:25:02.000 NULL 1900-01-01 12:25:02.000 NULL NULL
6 张三 1900-01-01 21:25:02.000 NULL NULL NULL 1900-01-01 21:25:02.000
7 李四 1900-01-01 07:25:02.000 1900-01-01 07:25:02.000 NULL NULL NULL
7 李四 1900-01-01 12:25:02.000 NULL 1900-01-01 12:25:02.000 NULL NULL
7 李四 1900-01-01 17:20:02.000 NULL NULL 1900-01-01 17:20:02.000 NULL
7 李四 1900-01-01 21:52:02.000 NULL NULL NULL 1900-01-01 21:52:02.000
8 赵六 1900-01-01 07:28:02.000 1900-01-01 07:28:02.000 NULL NULL NULL
8 赵六 1900-01-01 12:25:54.000 NULL 1900-01-01 12:25:54.000 NULL NULL
8 赵六 1900-01-01 17:54:54.000 NULL NULL 1900-01-01 17:54:54.000 NULL
8 赵六 1900-01-01 21:12:43.000 NULL NULL NULL 1900-01-01 21:12:43.000
NULL NULL NULL NULL NULL NULL NULL