SQL Sql查询创建计算字段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25915156/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Sql query to create a calculated field
提问by eviB
I have a database table like this:
I hope I can explain this well, so you can understand.
我有一个这样的数据库表：
希望我能解释得很好，这样你就可以理解了。
I want to calculate how many hours each employee has worked.
For example for "Arjeta Domi" we have Cell(2,3) - Cell(3,3) + Cell(4,3) + Cell(5,3), making the difference of each logOut time with Login time.
我想计算每个员工工作了多少小时。
例如，对于“Arjeta Domi”，我们有 Cell(2,3) - Cell(3,3) + Cell(4,3) + Cell(5,3)，使每次注销时间与登录时间不同。
The final table that I want will have these columns: CardNo, UserName, Date, PauseTime, WorkTime
我想要的最终表将包含以下列：CardNo, UserName, Date, PauseTime,WorkTime
I tried this query: taken from the 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
The DateTimeColumn is of type String, not DateTime.
I am working with MS Access Database.
该DateTime列的类型的String，不是DateTime。我正在使用 MS Access 数据库。
采纳答案by bummi
Select all rows with 'm001-1-In' with DateTime as I and add the fitting 'm001-1-Exit' rows to this with a Subquery as O, this will look like this:
选择所有带有 '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'
Now it's easy to encapsulate this, show as Prepared below and add our SUM and Grouping to this:
现在很容易封装它，如下所示，并添加我们的 SUM 和分组：
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]
If you need to restrict the DateRange you add the needed conditions to the row where t1.Addr='m001-1-In'
如果您需要限制 DateRange，则将所需的条件添加到行 where t1.Addr='m001-1-In'
回答by niyou
Try this:
尝试这个：
SELECT CardNo, UserName, SUM(DATEDIFF('h',DateTime,(SELECT MIN(Datetime)
FROM table as t2
WHERE t1.CardNo=t2.CardNo
AND t2.Addr like '%Exit'
AND t2.DateTime > t1.DateTime )))
FROM table as t1
WHERE Addr like '%In'
GROUP BY CardNo, UserName
