快速行合并后更新

[landlordh]

表1(卡位记录)
p d t
001 2007-01-01 07:30
001 2007-01-01 12:31
001 2007-01-01 12:30
001 2007-01-02 13:30
002 2007-01-01 07:30
002 2007-01-01 12:30
003 2007-01-03 07:30

表2(结果)
p d t1 t2 t3 ....
001 2007-01-01 07:30 12:31
002 2007-01-01 07:30 12:30
001 2007-01-02 13:30
003 2007-01-03 07:30

说明:(兼容sql2000&2005)
表1中数据按p和d分组,
多行合并为一行时,如果有卡位就按先后顺序插入到表2中
(5分钟之内只选其中第一个[t1]或最后一个[t2])
如果表2中t为单数就选其中第一个,
如果表2中t是双数就选其中最后一个,t字段一共有10个左右

用游标的方法太慢了,因为数据量太大

[paoluo]

--如果間隔5分鐘有多條數據，這裡取的是第一條。

Select * Into #T1 From 表1 A
Where Not Exists(Select p From 表1 Where p = A.p And d = A.d And DateDiff(mi, d + ' ' + t, A.d + ' ' + A.t) Between 0 And 5 And t != A.t)

Select OrderID = (Select Count(p) From #T1 Where p = A.p And d = A.d And t <= A.t), * Into #T2 From #T1 A

Declare @S Varchar(8000)
Select @S = 'Select p, d'
Select @S = @S + ', Max(Case OrderID When ' + Cast(OrderID As Varchar) + ' Then t Else '''' End) As t' + Cast(OrderID As Varchar)
From #T2 Group By OrderID
Select @S = @S +' From #T2 Group By p, d'
EXEC(@S)

Drop Table #T1, #T2

[paoluo]

--如果間隔5分鐘有多條數據，這裡取的是第一條。

--創建測試環境
Create Table 表1
(p Char(3),
d Varchar(10),
t Varchar(5))
Insert 表1 Select '001', '2007-01-01', '07:30'
Union All Select '001', '2007-01-01', '12:31'
Union All Select '001', '2007-01-01', '12:30'
Union All Select '001', '2007-01-02', '13:30'
Union All Select '002', '2007-01-01', '07:30'
Union All Select '002', '2007-01-01', '12:30'
Union All Select '003', '2007-01-03', '07:30'
GO
--測試
Select * Into #T1 From 表1 A
Where Not Exists(Select p From 表1 Where p = A.p And d = A.d And DateDiff(mi, d + ' ' + t, A.d + ' ' + A.t) Between 0 And 5 And t != A.t)

Select OrderID = (Select Count(p) From #T1 Where p = A.p And d = A.d And t <= A.t), * Into #T2 From #T1 A

Declare @S Varchar(8000)
Select @S = 'Select p, d'
Select @S = @S + ', Max(Case OrderID When ' + Cast(OrderID As Varchar) + ' Then t Else '''' End) As t' + Cast(OrderID As Varchar)
From #T2 Group By OrderID
Select @S = @S +' From #T2 Group By p, d'
EXEC(@S)

Drop Table #T1, #T2
GO
--刪除測試環境
Drop Table 表1
--結果
/*
p d t1 t2
001 2007-01-01 07:30 12:30
002 2007-01-01 07:30 12:30
001 2007-01-02 13:30
003 2007-01-03 07:30
*/

[landlordh]

谢谢回复先,此临时表方法比游标要快很多.
目前你是--如果間隔5分鐘有多條數據，始终取的是第一條。
如果表2中卡位为单数就选(間隔5分鐘多條數據中第一个),
如果表2中卡位是双数就选(間隔5分鐘多條數據中末一个),

如果这里改进一下就可以满足我的需要,
Insert 表1 Select '001','2007-01-01','08:00' --这里会到t1卡位
Union All Select '001','2007-01-01','12:02' --这里丢弃
Union All Select '001','2007-01-01','12:03' --这里会到t2卡位(5分钟内取后一个)
Union All Select '001','2007-01-01','13:30' --这里会到t3卡位(5分钟内取第一个)
Union All Select '001','2007-01-01','13:31' --这里丢弃
Union All Select '001','2007-01-01','18:01' --这里会到t4卡位
Union All Select '001','2007-01-02','13:30'
Union All Select '002','2007-01-01','07:30'
Union All Select '002','2007-01-01','12:30'
Union All Select '003','2007-01-03','07:30'

[landlordh]

示例数据如下:
--創建測試環境
Create Table 表1(
p Char(3),
d Varchar(10),
t Varchar(5)
)
Insert 表1 Select '001','2007-01-01','08:00' --这里会到t1卡位
Union All Select '001','2007-01-01','09:02' --这里丢弃,不在表3的某个(t卡位士30分钟)之内
Union All Select '001','2007-01-01','12:02' --这里丢弃
Union All Select '001','2007-01-01','12:03' --这里会到t2卡位(5分钟内取后一个)
Union All Select '001','2007-01-01','13:30' --这里会到t3卡位
Union All Select '001','2007-01-02','13:30' --这里会到t3卡位(5分钟内取第一个)
Union All Select '001','2007-01-02','13:31' --这里丢弃
Union All Select '001','2007-01-02','18:01' --这里会到t4卡位
Union All Select '002','2007-01-01','07:30' --算到前一天的跨天早退下班
Union All Select '002','2007-01-01','19:50'
Union All Select '002','2007-01-02','08:03'
Union All Select '003','2007-01-03','08:00'
Union All Select '003','2007-01-02','15:30'
GO

Create Table 表3(
p Char(3), d Varchar(10),
t1 Varchar(6), s1 varchar(2),
t2 Varchar(6), s2 varchar(2),
t3 Varchar(6), s3 varchar(2),
t4 Varchar(6), s4 varchar(2),
t5 Varchar(6), s5 varchar(2),
t6 Varchar(6), s6 varchar(2),
t7 Varchar(6), s7 varchar(2),
t8 Varchar(6), s8 varchar(2),
t9 Varchar(6), s9 varchar(2),
t10 Varchar(6), s10 varchar(2)
)
Insert 表3 Select '001','2007-01-01',' 08:00','上',' 12:00','下',' 13:30','上',' 18:00','下','','','','','','','','','','','',''
Union All Select '001','2007-01-02',' 08:00','上',' 12:00','下',' 13:30','上',' 18:00','下','','','','','','','','','','','',''
Union All Select '002','2007-01-01',' 20:00','上','+08:00','下','','','','','','','','','','','','','','','',''
Union All Select '003','2007-01-03',' 07:30','上',' 15:30','下','','','','','','','','','','','','','','','',''
GO

Create Table 表2(
p Char(3), d Varchar(10),
t1 Varchar(5), s1 varchar(2),
t2 Varchar(5), s2 varchar(2),
t3 Varchar(5), s3 varchar(2),
t4 Varchar(5), s4 varchar(2),
t5 Varchar(5), s5 varchar(2),
t6 Varchar(5), s6 varchar(2),
t7 Varchar(5), s7 varchar(2),
t8 Varchar(5), s8 varchar(2),
t9 Varchar(5), s9 varchar(2),
t10 Varchar(5), s10 varchar(2),
ex varchar(1)
)
--Insert 表2 Select '001','2007-01-01','08:00','上','12:03','下','13:30','上','','下','','','','','','','','','','','','','0'
--Union All Select '001','2007-01-02','','上','','下','13:31','上','18:01','下','','','','','','','','','','','','','0'
--Union All Select '002','2007-01-01','19:50','上','08:03','下','','','','','','','','','','','','','','','','','1'
--Union All Select '003','2007-01-03','08:00','上','15:30','下','','','','','','','','','','','','','','','','','0'
GO

--select * from 表1
--select * from 表2
--select * from 表3

--刪除測試環境
Drop Table 表1,表2,表3

[landlordh]

间隔多条的问题我通过硬件控制了,始终取的是第一條
另外还有个问题需要补充,就是有的班次可能"跨天"
表3 (每个卡位时间点可向前和向后扩展30分钟)
p d t1 t2 t3 t4 ....
001 2007-01-01 08:00 12:00 13:30 18:00
002 2007-01-01 20:00 +08:00
001 2007-01-02 08:00 12:00
003 2007-01-03 07:30 12:00 14:30 18:00
根据表1(打卡记录)和表3(班次)来插入到表2(日报)中
如果表1的t打卡记录在表3的t1-30分钟与表3的t1+30分钟之间,
那么就插入到表2的t1

[paoluo]

能不能貼點示例數據和結果出來，最好能包含能所說的那些情況。

[landlordh]

示例:
表1(卡位记录,t为5位)
p d t
001 2007-01-01 07:59
001 2007-01-01 12:02
001 2007-01-02 13:31
001 2007-01-02 18:03
002 2007-01-01 19:40
002 2007-01-02 08:01
003 2007-01-03 07:30

表3(班次记录 t字段为6位,前跨为-,后跨为+,不跨为空格)
p d t1 s1 t2 s2 t3 s3 t4 s4 ....
001 2007-01-01 08:00 上 12:00 下 13:30 上 18:00 下
001 2007-01-02 08:00 上 12:00 下 13:30 上 18:00 下
002 2007-01-01 20:00 上 +08:00 下
003 2007-01-03 07:30 上 12:00 下 14:30 上 18:00 下

表2(结果,t字段为5位,在班次卡位内插入,如果没有迟到早退旷工等异常情况ex=1)
p d t1 s1 t2 s2 t3 s3 t4 .... ex
001 2007-01-01 07:59 上 12:02 上 上 0
002 2007-01-01 19:40 上 08:01 跨天下 1
001 2007-01-02 上 下 13:31 上 18:03 0
003 2007-01-03 07:30 上 下 上 0

[landlordh]

加上可能存在的重复记录的情况
Insert 表1 Select '001','2007-01-01','08:00' --这里会到t1卡位
Union All Select '001','2007-01-01','09:02' --这里丢弃,不在表3的某个t卡位-+30分钟之内
Union All Select '001','2007-01-01','12:02' --这里丢弃
Union All Select '001','2007-01-01','12:03' --这里会到t2卡位(5分钟内取后一个)
Union All Select '001','2007-01-01','13:30' --这里会到t3卡位(5分钟内取第一个)
Union All Select '001','2007-01-01','13:31' --这里丢弃
Union All Select '001','2007-01-01','18:01' --这里会到t4卡位
Union All Select '001','2007-01-02','13:30'
Union All Select '002','2007-01-01','07:30'
Union All Select '002','2007-01-01','12:30'
Union All Select '003','2007-01-03','07:30'
注:
如果表1的t打卡记录在(表3的t1-30分钟与表3的t1+30分钟)之间才插入到表2的t1
同理t2,t3,t4,t5,t6,t7,t8,t9,t10