--显示
select perid,date1,
max(case px when 1 then time end) k1,
max(case px when 2 then time end) k2,
max(case px when 3 then time end) k3,
max(case px when 4 then time end) k4,
max(case px when 5 then time end) k5,
max(case px when 6 then time end) k6
from
(
select px=(select count(1) from A where perid = t.perid and date1 = t.date1 and time<t.time)+1 , * from a t
) m
group by perid,date1--更新
update b
set k1 = n.k1,k2 = n.k2,k3 = n.k3,k4 = n.k4,k5 = n.k5,k6 = n.k6
from b,(select perid,date1,
max(case px when 1 then time end) k1,
max(case px when 2 then time end) k2,
max(case px when 3 then time end) k3,
max(case px when 4 then time end) k4,
max(case px when 5 then time end) k5,
max(case px when 6 then time end) k6
from
(
select px=(select count(1) from A where perid = t.perid and date1 = t.date1 and time<t.time)+1 , * from a t
) m
group by perid,date1) n
where b.perid = n.perid and b.date2 = n.date1
select perid,date1,
max(case px when 1 then time end) k1,
max(case px when 2 then time end) k2,
max(case px when 3 then time end) k3,
max(case px when 4 then time end) k4,
max(case px when 5 then time end) k5,
max(case px when 6 then time end) k6
from
(
select px=(select count(1) from A where perid = t.perid and date1 = t.date1 and time<t.time)+1 , * from a t
) m
group by perid,date1--更新
update b
set k1 = n.k1,k2 = n.k2,k3 = n.k3,k4 = n.k4,k5 = n.k5,k6 = n.k6
from b,(select perid,date1,
max(case px when 1 then time end) k1,
max(case px when 2 then time end) k2,
max(case px when 3 then time end) k3,
max(case px when 4 then time end) k4,
max(case px when 5 then time end) k5,
max(case px when 6 then time end) k6
from
(
select px=(select count(1) from A where perid = t.perid and date1 = t.date1 and time<t.time)+1 , * from a t
) m
group by perid,date1) n
where b.perid = n.perid and b.date2 = n.date1
解决方案 »
- SQL server安装求教
- 这样的SQL怎么写???????????
- select top 60000 code=identity(int,1,1) into #t4 from syscolumns a,syscolumns b
- 存储过程返回值问题
- 头想痛了,狗(((日的交叉表还原问题!
- sql server 2008 R2运行一段时间后变得很慢
- 级联更新的问题
- 各位,请教一个问题:
- select * from table3 where datediff(d,:ymda,dt)>=0 and datediff(d,dt,:ymdb)>=0还是不行
- sql server 的存储大容量2进制文件问题?
- 急!!!请救一个SQL标识列的问题~~~~~~~~~~~~~~~~~
- sql server突然连不上了
insert into a values(1,'2007-10-11','01:00')
insert into a values(1,'2007-10-11','02:00')
insert into a values(1,'2007-10-11','03:00')
insert into a values(1,'2007-10-11','04:00')
insert into a values(1,'2007-10-11','05:00')
insert into a values(1,'2007-10-11','06:00')
insert into a values(1,'2007-10-12','07:00')
insert into a values(1,'2007-10-12','08:00')
insert into a values(1,'2007-10-12','09:00')
insert into a values(1,'2007-10-12','10:00')
insert into a values(1,'2007-10-12','11:00')
insert into a values(1,'2007-10-12','12:00')
create table b(perid int,date2 varchar(10),k1 varchar(10),k2 varchar(10),k3 varchar(10),k4 varchar(10),k5 varchar(10),k6 varchar(10))
insert into b values(1,'2007-10-11',null,null,null,null,null,null)
insert into b values(1,'2007-10-12',null,null,null,null,null,null)
go
--a表数据
select * from a
/*
perid date1 time
----------- ---------- ----------
1 2007-10-11 01:00
1 2007-10-11 02:00
1 2007-10-11 03:00
1 2007-10-11 04:00
1 2007-10-11 05:00
1 2007-10-11 06:00
1 2007-10-12 07:00
1 2007-10-12 08:00
1 2007-10-12 09:00
1 2007-10-12 10:00
1 2007-10-12 11:00
1 2007-10-12 12:00
(所影响的行数为 12 行)
*/--b表数据
select * from b
/*
perid date2 k1 k2 k3 k4 k5 k6
----------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1 2007-10-11 NULL NULL NULL NULL NULL NULL
1 2007-10-12 NULL NULL NULL NULL NULL NULL
(所影响的行数为 2 行)
*/--显示结果
select perid,date1,
max(case px when 1 then time end) k1,
max(case px when 2 then time end) k2,
max(case px when 3 then time end) k3,
max(case px when 4 then time end) k4,
max(case px when 5 then time end) k5,
max(case px when 6 then time end) k6
from
(
select px=(select count(1) from A where perid = t.perid and date1 = t.date1 and time<t.time)+1 , * from a t
) m
group by perid,date1
/*
perid date1 k1 k2 k3 k4 k5 k6
----------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1 2007-10-11 01:00 02:00 03:00 04:00 05:00 06:00
1 2007-10-12 07:00 08:00 09:00 10:00 11:00 12:00
(所影响的行数为 2 行)
*/
--更新update b
set k1 = n.k1,k2 = n.k2,k3 = n.k3,k4 = n.k4,k5 = n.k5,k6 = n.k6
from b,(select perid,date1,
max(case px when 1 then time end) k1,
max(case px when 2 then time end) k2,
max(case px when 3 then time end) k3,
max(case px when 4 then time end) k4,
max(case px when 5 then time end) k5,
max(case px when 6 then time end) k6
from
(
select px=(select count(1) from A where perid = t.perid and date1 = t.date1 and time<t.time)+1 , * from a t
) m
group by perid,date1) n
where b.perid = n.perid and b.date2 = n.date1
select * from b
/*
perid date2 k1 k2 k3 k4 k5 k6
----------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1 2007-10-11 01:00 02:00 03:00 04:00 05:00 06:00
1 2007-10-12 07:00 08:00 09:00 10:00 11:00 12:00
(所影响的行数为 2 行)
*/
drop table a,b
基本解决了,刚有朋友修改了一下我的触发器,现在可以更新了^_^
CREATE trigger chuqin
on dbo.指纹打卡
for insert
asdeclare @perid varchar(20)
declare @date1 datetime
declare @time1 varchar(5)beginselect @perid=人事编号,@date1=日期,@time1=时间 from insertedif datepart(hh,convert(smalldatetime,@time1))>=0 and datepart(hh,convert(smalldatetime,@time1))<5
update 考勤记录 set 刷卡6=a2.时间 from 考勤记录 a1,inserted a2 where a1.刷卡日期=a2.日期 and a1.人事编号=a2.人事编号
end