如何使用sql为每个日期选择第一条记录 [英] How can Select the first record for each date using sql

问题描述

我正在尝试为每位员工选择一个时间列表的平均值：



我的表：出勤率设备

I'm trying to select the average of a list of timesIn for each employee:

My table: AttendanceByDevice

enrollNumber   DateAttendance     TimesInOut         Mode  
40              20-07-2015        08:00:00            0  
40              20-07-2015        10:00:00            1  
40              20-07-2015        12:00:00            0  
40              20-07-2015        17:00:00            1  
40              21-07-2015        08:00:00            0  
40              21-07-2015        11:00:00            1  
40              21-07-2015        13:00:00            0  
40              21-07-2015        18:00:00            1  

InOutMode = 0表示CheckIn

InOutMode = 1表示CheckOut

我需要选择第一次的平均值TimesInOut（其中Mode = 0）表示每位员工和每个日期。

示例：员工的TimesInOut平均值= aveareg（2015年7月20日的第一次TimesInOut和2015年7月21日的第一次TimesInOut，其中Mode = 0）

InOutMode =0 it means CheckIn
InOutMode=1 it means CheckOut
I need to select the average of the firsts TimesInOut(Where Mode=0) for each employee and for each date.
example: the average of TimesInOutfor the employee 40 = aveareg (first TimesInOut of 20-07-2015 and first TimesInOut of 21-07-2015 where Mode=0)

SELECT convert(varchar(5),CAST(AVG(CAST(TimesInOut AS real) - FLOOR(CAST(TimesInOut as real))) AS datetime),108)
FROM Log_Attendance where  Mode=0 and  EnrollNumber =40

任何解决方案都可以吗？

any solution please?

推荐答案

分组和聚合egates将满足您的需求。



组通过显示唯一列值来工作，但实际上其余列在该组中聚合。您可以在这些列上使用聚合函数来显示min，最小值，最大值，最高值，平均值，所有聚合的平均值，计数以显示聚合数等等...



有些聚合只能在数字字段上执行，例如avg，但大多数其他聚合只适用于任何字段类型。



Min将使用字符串格式08:30:00，但它将按字典顺序工作。这意味着它将从最左边的字符开始执行字符测试：只要它们是varchar而不是int：9将高于10，99将高于10000000但09将会低于10.



这样，它会按你的时间格式工作，因为你有前导零：

Hi,

Grouping and aggregates will suit your needs.

Groups work by showing a unique column value but actually the rest of the columns are aggregated in that group. You can use aggregate functions on those columns to show min, the lowest of the aggregate values, max, that highest, avg, average of all aggregates, count to show the number of aggregates and so on...

Some aggregates can only be performed on numeric fields, such as avg, but most other will work on any field type.

Min will work on the string format 08:30:00 but it will work "lexicographically". This means that it will perform a character test starting with the left most character: so as long as they are varchar and not int: "9" will be higher that "10" and "99" will be higher that "10000000" but 09 will be lower that 10.

This being so, it will work on your time format because you have leading zeros:

Select
    enrollNumber,   --Non aggregate field
    DateAttendance, --Non aggregate field
    Min(TimesInOut) as FirstTimeIn, --aggregate field
From table
Where 
    Mode=0
Group By --anything here is a non aggregate field so you can't use aggregates on them
    enrollNumber, 
    DateAttendance
--Anything not in the group by section MUST have an aggregate function
--If the aggregate rules are not met then you'll get an error complaining about aggregates

作为旁边：在Group By之后，您可以包含 HAVING 部分。这类似于 WHERE 部分，但您可以在 WHERE 部分中包含不能包含的聚合字段。



您可以 HAVING EnrollNumber = 40 但这样效率不高。最好将其保存在 WHERE 部分中，以便在分组之前将其排除。



我用它来查找重复项，所以我可能有 HAVING count（*）> 1







希望有所帮助^ _ ^



Andy

Just as an aside: After the Group By you can include the HAVING section. This works like the WHERE section but you can include aggregate fields that you cannot in the WHERE section.

You could have HAVING EnrollNumber = 40 but that would not be efficient. It's best to keep that in the WHERE section so it's excluded before the grouping.

I use it to find duplicates so I might have HAVING count(*) > 1



Hope that helps ^_^

Andy

我认为（并希望）无论您将日期和时间存储在单个日期时间列中的格式如何。



不确定我是否正确理解了这个问题但是...



现在获得最低模式您按日期分组的每日员工的0条记录，并选择最小值，例如

I take it (and hope) that regardless of the formatting you're storing the date and time in a single datetime column.

Not sure if I understood the question correctly but...

Now to get the minimum mode 0 records for an employee per each day you would group by date and select the minimum, for example

SELECT MIN(AttendanceDate) 
FROM Log_Attendance
WHERE EnrollNumber = 40
AND   Mode = 0
GROUP BY CAST(AttendanceDate AS date)

正如所说的AttendaceDate应该包含日期和时间。



这很容易，但是你不能计算日期的平均值，所以问题的一部分没有没有意义。

As said AttendaceDate should contain both date and time.

This far it's easy but you can't calculate average for dates so that part of the question doesn't quite make sense.

这篇关于如何使用sql为每个日期选择第一条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！