A表如数据
001用户刷卡的记录上班下班刷卡有两条记录
SwipeDateTime
-----------------------
2011-05-03 07:30:23.000 第一条记录
2011-05-02 12:12:23.000 第二条记录B表
ClassUP1 ClassNext1
2011-05-03 07:30:23.000 2011-05-02 12:12:23.000A表第一条记录插入到B表ClassUP1
怎么在有这第二条记录时把他插入到B表ClassNext1
这个要怎么做
001用户刷卡的记录上班下班刷卡有两条记录
SwipeDateTime
-----------------------
2011-05-03 07:30:23.000 第一条记录
2011-05-02 12:12:23.000 第二条记录B表
ClassUP1 ClassNext1
2011-05-03 07:30:23.000 2011-05-02 12:12:23.000A表第一条记录插入到B表ClassUP1
怎么在有这第二条记录时把他插入到B表ClassNext1
这个要怎么做
declare @t table(userNumber int,swipeDateTime datetime);
insert into @t select '001','2011-05-03 07:30:23.000' union all select '001','2011-05-02 12:12:23.000'
union all select '002','2011-05-03 07:30:23.000' union all select '002','2011-05-02 12:12:23.000';;with cte as
(
select userNumber,swipeDateTime,rn=ROW_NUMBER() over(partition by userNumber order by swipeDateTime ) from @t
)
select * from cte a join cte b on a.userNumber=b.userNumber and a.rn=b.rn+1;-- 如果是定了有两条可以用排序,最好是有个上下班标识多好。
服务器: 消息 156,级别 15,状态 1,行 4
在关键字 'with' 附近有语法错误。
服务器: 消息 195,级别 15,状态 1,行 6
'ROW_NUMBER' 不是可以识别的 函数名。
max(case cid when '2' then swipeDateTime else '' end) as 'classNext1'
from T group by UserNumber
INSERT dbo.A (usernumber,swipedatetime)
SELECT '001', '2011-05-03 07:30:23' UNION ALL
SELECT '001', '2011-05-02 12:12:23' CREATE TABLE B(usernumber VARCHAR(10),classup1 DATETIME,classnext1 DATETIME)INSERT dbo.B
SELECT a1.usernumber ,
a1.swipedatetime ,
a2.swipedatetime
FROM dbo.A a1
JOIN dbo.A a2 ON a1.id = a2.id - 1
AND a1.usernumber = a2.usernumberSELECT * FROM dbo.B
/*
usernumber classup1 classnext1
---------- ----------------------- -----------------------
001 2011-05-03 07:30:23.000 2011-05-02 12:12:23.000
(1 行受影响)
*/
看到楼主的其他帖子,好像A表是有主键ID的,因此自己加了一个ID,但是这样的语句楼主是更新不了你的EmployeesAttendance的表的,楼主参考吧~~
select 用户 , min(SwipeDateTime) , max(SwipeDateTime) from a group by 用户
INSERT dbo.A (usernumber,swipedatetime)
SELECT '001', '2011-05-03 07:30:23' UNION ALL
SELECT '003', '2011-05-02 12:12:23' UNION ALL
SELECT '002', '2011-05-03 07:30:23'UNION ALL
SELECT '004', '2011-05-02 12:12:23' UNION ALL
SELECT '003', '2011-05-03 07:30:23' UNION ALL
SELECT '001', '2011-05-02 12:12:23' CREATE TABLE B(usernumber VARCHAR(10),classup1 DATETIME,classnext1 DATETIME)INSERT dbo.B
SELECT a1.usernumber ,
a1.swipedatetime ,
a2.swipedatetime
FROM dbo.A a1
JOIN dbo.A a2 ON a1.id = a2.id - 1
AND a1.usernumber = a2.usernumberSELECT * FROM dbo.a
这样的话,我要以ID列为单数在B表插入classup1列,双数插入在B表classnext1这一列.我除模进就不行了.
(
select * ,row_number() over(partition by usernumber order by swipedatetime) as cid from a
)
insert into b select usernumber ,max(case cid when '1' then swipedatetime else null end ) as 'classUp1',
max(case cid when '2' then swipedatetime else null end ) as 'classNext'
from cte group by usernumber
select usernumber,min(swipedatetime) from a group by usernumber
update b set classnext1=(select max(swipedatetime) from a group by usernumber)
from a,b where a.usernumber=b.usernumber
INSERT dbo.A (usernumber,swipedatetime)
SELECT '001', '2011-05-02 07:30:23' UNION ALL
SELECT '003', '2011-05-02 12:12:23' UNION ALL
SELECT '003', '2011-05-02 07:30:23' UNION ALL
SELECT '001', '2011-05-02 12:12:23'
create table b(usernumber VARCHAR(10),classup1 datetime,classnext1 datetime)insert into b(usernumber,classup1)
select usernumber,min(swipedatetime) from a group by usernumber
update b set classnext1=(select max(swipedatetime) from a where a.usernumber=b.usernumber group by a.usernumber)
from a,b where a.usernumber=b.usernumber
感觉还是以刷卡日期来判断上下班为好通过convert(varchar(2),getdate(),108) --取出打卡时间的【小时】部分;
然后通过【小时】判断 是上午还是下午
--值开始
(
select SwipeDateTime from (
select IDD=row_number()over(order by getdate()),SwipeDateTime from (select top 100 SwipeDateTime from AttendanceTimeRecords order by Numbers)temp
)t where IDD%2<>0
) --值结束
where EmployeesAttendance.Numbers=AttendanceTimeRecords.Numbers
错误提示
Msg 4104, Level 16, State 1, Line 1
无法绑定由多个部分组成的标识符 "AttendanceTimeRecords.Numbers"。