送分!!!急呀

Question

/*
返回时间区域代码
*/
CREATE  FUNCTION [dbo].[GetCheckOnCode] 
(
@time VARCHAR(50)
)  
RETURNS VARCHAR(50)
AS  
BEGIN 
DECLARE @str VARCHAR(50)
DECLARE @D SMALLDATETIME
SET @D=CONVERT(SMALLDATETIME,@time,8)
IF DATEDIFF(SS,'00:00:00',@D)>=0 AND DATEDIFF(SS,'12:00:00',@D)<=0
SET @str='time1'
ELSE IF DATEDIFF(SS,'12:00:00',@D)>=0 AND DATEDIFF(SS,'17:00:00',@D)<=0
SET @str='time2'
ELSE IF DATEDIFF(SS,'17:00:00',@D)>=0 AND DATEDIFF(SS,'23:00:00',@D)<=0
SET @str='time3'
ELSE
SET @str='time4'
RETURN @str
END
gocreate table t1(no1 varchar(10),datetime1 datetime)
go
insert t1 values('001','2007-03-04 07:50')
insert t1 values('001','2007-03-04 13:25')
insert t1 values('001','2007-03-04 17:50')
insert t1 values('002','2007-03-04 07:50')
insert t1 values('002','2007-03-04 13:22')
insert t1 values('002','2007-03-04 17:56')
goselect * from t1
go
select no1,[dd],
[time1]=max(case when tag='time1' then tt end),
[time2]=max(case when tag='time2' then tt end),
[time3]=max(case when tag='time3' then tt end)
from (
select no1,[dd]=CONVERT(VARCHAR(10),datetime1,120),[tt]=CONVERT(VARCHAR(10),datetime1,8),
[tag]=[dbo].[GetCheckOnCode](CONVERT(VARCHAR(10),datetime1,8))
from t1
) t
group by no1,[dd]
godrop table t1
go

Answer 1

no1        datetime1                                              
---------- ------------------------------------------------------ 
001        2007-03-04 07:50:00.000
001        2007-03-04 13:25:00.000
001        2007-03-04 17:50:00.000
002        2007-03-04 07:50:00.000
002        2007-03-04 13:22:00.000
002        2007-03-04 17:56:00.000（所影响的行数为 6 行）no1        dd         time1      time2      time3      
---------- ---------- ---------- ---------- ---------- 
001        2007-03-04 07:50:00   13:25:00   17:50:00
002        2007-03-04 07:50:00   13:22:00   17:56:00

Answer 2

--如果每天的time數目是不確定的，使用動態SQL語句Create Table t1
(no Char(3),
 time DateTime)
Insert t1 Select  '001',   '2007-03-04 07:50'
Union All Select '001',   '2007-03-04 13:25'
Union All Select '001',   '2007-03-04 17:50'
Union All Select '002',   '2007-03-04 07:50'
Union All Select '002',   '2007-03-04 13:22'
Union All Select '002',   '2007-03-04 17:56'
GO
Declare @S Varchar(8000)
Select @S = 'Select no, [date]'
Select @S = @S + ', Max(Case OrderID When ' + Rtrim(OrderID) + ' Then [time] Else '''' End) As time' + Rtrim(OrderID)
From (Select *, OrderID =(Select Count(*) From t1 Where no = A.no And DateDiff(dd, [time], A.[time]) = 0 And [time] <= A.[time]) From t1 A) B Group By OrderID
Select @S = @S + ' From (Select no, Convert(Varchar(10), [time], 120) As [date], Convert(Varchar(5), [time], 108) As [time], OrderID =(Select Count(*) From t1 Where no = A.no And DateDiff(dd, [time], A.[time]) = 0 And [time] <= A.[time]) From t1 A) B Group By no, [date]'
EXEC(@S)
GO
Drop Table t1
--Result
/*
no date time1 time2 time3
001 2007-03-04 07:50 13:25 17:50
002 2007-03-04 07:50 13:22 17:56
*/

Answer 3

--如果每天的time數目是不確定的，使用動態SQL語句
--再加入一條數據Create Table t1
(no Char(3),
 time DateTime)
Insert t1 Select  '001',   '2007-03-04 07:50'
Union All Select '001',   '2007-03-04 13:25'
Union All Select '001',   '2007-03-04 17:50'
Union All Select '002',   '2007-03-04 07:50'
Union All Select '002',   '2007-03-04 13:22'
Union All Select '002',   '2007-03-04 17:56'
Union All Select '002',   '2007-03-04 18:56'
GO
Declare @S Varchar(8000)
Select @S = 'Select no, [date]'
Select @S = @S + ', Max(Case OrderID When ' + Rtrim(OrderID) + ' Then [time] Else '''' End) As time' + Rtrim(OrderID)
From (Select *, OrderID =(Select Count(*) From t1 Where no = A.no And DateDiff(dd, [time], A.[time]) = 0 And [time] <= A.[time]) From t1 A) B Group By OrderID
Select @S = @S + ' From (Select no, Convert(Varchar(10), [time], 120) As [date], Convert(Varchar(5), [time], 108) As [time], OrderID =(Select Count(*) From t1 Where no = A.no And DateDiff(dd, [time], A.[time]) = 0 And [time] <= A.[time]) From t1 A) B Group By no, [date]'
EXEC(@S)
GO
Drop Table t1
--Result
/*
no date time1 time2 time3 time4
001 2007-03-04 07:50 13:25 17:50
002 2007-03-04 07:50 13:22 17:56 18:56
*/

Answer 4

declare @sql varchar(8000),@c int,@ct1 int
select top 1 @ct1=count(datetime1) from t1 group by no1 order by count(datetime1) desc
set @sql=''
set @c=1
while @c<=@ct1
select @sql=@sql+
',(select datetime1 from t1 a where no1=b.no1 and (select count(1) from t1 where no1=b.no1 and datetime1<=a.datetime1)='+
cast(@c as varchar(2))+') datetime'+cast(@c as varchar(2)),@c=@c+1
exec('select no1'+@sql+' from t1 b group by no1')

Answer 5

在我以前碰到这样的考勤问题，因打卡特殊原因，使用游标来完成。
方法：
DECLARE cursor_Org CURSOR FOR
    SELECT * FROM 记录表 
OPEN cursor_Org
FETCH NEXT FROM cursor_Org INTO 变量列表
WHILE
  BEGIN
     /*     构造更新语句、每50行更新一次
     */
     FETCH NEXT FROM cursor_Org INTO 变量列表
  END
CLOSE cursor_Org
DEALLOCATE
测试一下小厂：
800(人)*6(每天打卡数)*31(一个月的天数)=148800(条打卡记录)
在没有优化的情况下：
当时非常处理考全厂800人一个月考勤简单的原始打卡记录显示，差不多用了4分钟。
优化了，使用不到30秒。一个大厂：
5000(人)*6(每天打卡数)*31(一个月的天数)=930000(条打卡记录)
优化的情况下：使用2分钟左右。