SQL查询创建计算字段

Question

我有一个这样的数据库表:

我希望我能很好地解释这一点，以便您能理解。

我想计算每个员工工作了多少小时。
例如，对于“Arjeta Domi”，我们有 Cell(2,3) - Cell(3,3) + Cell(4,3) + Cell(5,3)，使每个注销时间与登录时间不同。

我想要的最终表将包含这些列:CardNo、UserName、Date、PauseTime、 工作时间

我试过这个查询:取自 duplicate

SELECT DISTINCT
  [Card NO], 
  [User Name],  
  (
    SELECT
      MIN(DateTime) AS [Enter Time], 
      MAX(DateTime) AS [Exit Time], 
      MAX(DateTime) - MIN(DateTime) AS [Inside Hours] 
    FROM
      ExcelData
  ) 
FROM
  ExcelData
GROUP BY
  [Card NO], [User Name], DateTime

DateTime 列的类型是 String，而不是 DateTime。我正在使用 MS Access 数据库。

Answer 1

选择所有包含 'm001-1-In' 且 DateTime 为 I 的行，并将合适的 'm001-1-Exit' 行添加到其中，子查询为 O，这将如下所示:

SELECT t1.[Card No], t1.[User Name],dateTime as I
,(Select TOP 1 dateTime from Tab t2 where  t2.[Card No]= t1.[Card No] 
and  t2.[User Name]= t1.[User Name] and  t2.Addr='m001-1-Exit' 
and t2.DateTime>t1.datetime ORDER by DateTime) as O
FROM Tab t1
where t1.Addr='m001-1-In' 

现在很容易封装它，如下所示 Prepared 并将我们的 SUM 和 Grouping 添加到此:

SELECT [Prepared].[Card No], [Prepared].[User Name], SUM(DateDiff('n',I,O))/60 AS Hours
FROM (
      SELECT t1.[Card No], t1.[User Name],dateTime as I
    ,(Select TOP 1 dateTime from Tab t2 where  t2.[Card No]= t1.[Card No] 
      and  t2.[User Name]= t1.[User Name] and  t2.Addr='m001-1-Exit' 
      and t2.DateTime>t1.datetime ORDER by DateTime) as O
FROM Tab t1
where t1.Addr='m001-1-In' 
)  AS [Prepared]
GROUP BY [Prepared].[Card No], [Prepared].[User Name]

如果您需要限制 DateRange，请将所需的条件添加到行 where t1.Addr='m001-1-In'