ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] SQL°ÝÃD

[µo°Ý] SQL°ÝÃD

¥H¤U¬O§Ú¹J¨ìªº°ÝÃD
½Ð°Ý ¦pªG¸ê®Æ®w¤¤ªº¸ê®Æ¤W¤U¯Z®É¶¡¬O¦b¦P¤@±Æ¤¤
¦Ó§Ú¥u¯à§ì¥X¤@¤Ñ ªº¤W¤U¯Z
½Ð°Ý
1.§Ú­n¦p¦ó ¥ÎÅý¥L¥Î°j°é¶]¥X§Ú­nªºµ²ªG?
2.§Ú¥[¤J¤U­±ªº¤èªk ³£·|¶]¥X¸ê®Æ®w¦s¦b#F³o¨Ç(§Ú¤]¸Õ¹Linsert into XXX ( ) Select  )
  WHILE(@VAR1<=31)
  BEGIN
  SET @day1 = DATEADD(day, 1, @day1)
  SET @VAR1 =@VAR1 + 1
  END



use demo_face
DECLARE @begintime2 as datetime
DECLARE @endtime2 as datetime
DECLARE @day as datetime

DECLARE @VAR1 int
set @day = '2016-01-23'
set @begintime2 = ' 00:00:00.000'
set @endtime2 = ' 23:59:00.000'


DROP TABLE #f2 ,#f3,#f4,#f5
Create table #f5 (UserNo char(50), ¤é´Á1 date,¤W¯Z®É¶¡ time, ¤U¯Z®É¶¡ time,workhours time,workhours1 int,workhours2 int)


SELECT [UserNo],[MatTime],[WorkTime] into #f2 FROM dbo.MF_Record where MatTime between @day+ @begintime2  and @day+@endtime2
select [UserNo],min(#f2.MatTime) as ¤W¯Z®É¶¡,max(#f2.MatTime) as ¤U¯Z®É¶¡,(max(#f2.MatTime)-min(#f2.MatTime)) as ¤u®É into #f3 from #f2 group by userno
select [UserNo],CONVERT(varchar(12),#f3.¤W¯Z®É¶¡,102) as ¤é´Á1,CONVERT(varchar(8),#f3.¤W¯Z®É¶¡,114) as ¤W¯Z®É¶¡,CONVERT(varchar(8),#f3.¤U¯Z®É¶¡,114) as ¤U¯Z®É¶¡,CONVERT(varchar(8),#f3.¤u®É,114) as ¤u®É into #f4 from #f3

INSERT INTO #f5(UserNo,¤é´Á1,¤W¯Z®É¶¡,¤U¯Z®É¶¡,workhours,workhours1,workhours2) select #f4.UserNo,CONVERT(varchar(12),#f4.¤é´Á1,102) as date, #f4.¤W¯Z®É¶¡ as duty,#f4.¤U¯Z®É¶¡ as offwork,#f4.¤u®É as workhours,datediff(HH,#f4.¤W¯Z®É¶¡,#f4.¤U¯Z®É¶¡) as workhours1,datediff(HH,#f4.¤W¯Z®É¶¡,#f4.¤U¯Z®É¶¡)-1 as workhours2 from #f4

select * from #f4

        ÀR«ä¦Û¦b : §Ú­Ì³Ì¤jªº¼Ä¤H¤£¬O§O¤H¡D¥i¯à¬O¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD