SQL查询中的用户日志数据处理 [英] Userlog data processing in SQL query

问题描述

我在sql中有一个UserLog表，其中包含UserName和DateTimeData字段。一个用户可以在一天内多次登录和注销。所以我必须生成一个视图或查询来显示X用户名和他/她的firstlogindatetime和lastlogoutdatetime以及该特定日期和特定用户的软件总使用情况。我必须为所有用户和日期重复同样的事情。最后，我需要编写查询以获取每个UserName by Date及其FirstLogin和LastLogout以及TotalTimeWorked。 UserName的我的数据类型是nvarchar（50），而DateTimeData的数据类型是nvarchar（50）。一个用户名每天只能出现一次。但它可以在单独的列中显示firstlogin和lastlogout。你们可以帮助我并引导我做这件事吗？

提前致谢:)



我尝试过：



我必须编写sql查询以获得所需的输出

I have one UserLog table in sql which contains UserName and DateTimeData fields. One user can login and logout several times in a day. So I have to generate one view or query to show the X username and his/her firstlogindatetime and lastlogoutdatetime and total usage of the software for that particular day and particular user. Same thing i have to repeat for all the users and dates. Finally, I need to write the query for get the each UserName by Date and their FirstLogin and LastLogout as well as TotalTimeWorked. My Data type for UserName is nvarchar(50) and for DateTimeData is nvarchar(50). One username should appear only once for day. But it can show firstlogin and lastlogout in separate columns. Can u guys pls help me and guide me in this thing?
Thanks in advance:)

What I have tried:

I have to write the sql query to get desired output

推荐答案

我会与评论中的人一样有点不同



- 我会写'n'个查询，其中一个查询可能是为用户获取数据x for day y

- 我使用PetaPoco提取数据并返回一个对象列表

- 然后我使用LINQ聚合（可能分组）对象



不幸的是，你必须先启动并为你决定使用的任何方法提供东西，即'纯sql'或基本的sql和LINQ - 这里的人不会为你编写代码

I would do this a 'bit differently' than the guys in the comments

- I'd write 'n' queries, where a query might be to get the data for user x for day y
- I'd use PetaPoco to extract that data and return an object list
- I'd then use LINQ to aggregate (possibly group) the objects

The unfortunate thing is, you have to make a start and supply 'something' for whichever approach you decide to use ie 'pure sql' or basic sql & LINQ - people here wont write your code for you

这篇关于SQL查询中的用户日志数据处理的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！