MS Access查询时间 [英] MS Access query for time

问题描述

跟踪数据并将MS Access与VB6一起使用

Following data and using MS Access with VB6

UserID  UserName  LogTime LogDate  

1       S         9:00    21/5/2010  
1       S         10:00   21/5/2010  
1       S         11:00   21/5/2010  
1       S         12:00   21/5/2010  
1       S         14:00   21/5/2010 
1       S         17:00   21/5/2010  

需要以下6列输出:-

1     S      21/5/2010 9:00  21/5/2010 10:00  
1     S      21/5/2010 11:00 21/5/2010 12:00  
1     S      21/5/2010 14:00 21/5/2010 17:00  

推荐答案

这不会很快:

SELECT x.UserID, x.UserName, y.LogTime, x.LogTime, y.LogDate
FROM (
      SELECT Test.UserID, Test.UserName, 
             Test.LogTime, Test.LogDate, 
             (SELECT Count(*) FROM Test t WHERE t.LogTime<=Test.LogTime) AS c
      FROM Test)  AS x 
INNER JOIN (
      SELECT Test.UserID, Test.UserName, 
             Test.LogTime, Test.LogDate, 
             (SELECT Count(*) FROM Test t WHERE t.LogTime<=Test.LogTime) AS c, 
             c+1 as k
      FROM Test)  AS y 
ON x.c = y.k
WHERE x.c Mod 2=0

这篇关于MS Access查询时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！