问题描述
例如，我想计算一天中员工ID的entrytype事件。
1表示第一个，2表示第二个，依此类推。
你能帮我解决一下如何在sql 5.7中做到这一点吗？
For example, I want to count the entrytype occurrences of an employee id in a day.
1 for the first in, 2 for the second in and so on.
Can you help me on how can I do this in sql 5.7?
entrytype id entrydate entry_count
--------- ------ ---------- ---------
IN 16 2018-04-16 1
OUT 16 2018-04-16 1
IN 16 2018-04-16 2
OUT 16 2018-04-16 2
IN 27 2018-04-16 1
OUT 27 2018-04-16 1
IN 27 2018-04-16 2
OUT 27 2018-04-16 2
IN 29 2018-04-16 1
OUT 29 2018-04-16 1
IN 29 2018-04-16 2
OUT 29 2018-04-16 2
我尝试过：
What I have tried:
SELECT entrytype, id, entrydate , COUNT(1) AS row_num FROM employeename2 GROUP BY id, entrytime ORDER BY entrydate,id;
但它显示
But it displays
entrytype id entrydate entry_count
--------- ------ ---------- ---------
IN 16 2018-04-16 1
OUT 16 2018-04-16 1
IN 16 2018-04-16 1
OUT 16 2018-04-16 1
IN 27 2018-04-16 1
OUT 27 2018-04-16 1
IN 27 2018-04-16 1
OUT 27 2018-04-16 1
IN 29 2018-04-16 1
OUT 29 2018-04-16 1
IN 29 2018-04-16 1
OUT 29 2018-04-16 1推荐答案
SELECT entrytype, id, entrydate , ROW_NUMBER() OVER (PARTITION BY id, entrytype, entrydate ORDER BY entrydate, id) FROM employeename2 其他几点：
- 你的表 employeename2 是不存储员工姓名 - 它存储出勤记录，应该适当命名。适当的名称将是任何不误导的名称，例如employeeAttendance，InOutRecord，XY0002349。 （最后一个不是没有字典的好名字，但至少它没有误导性。）
- 你桌子上的 id 列也不清楚 - 它实际上是员工ID 而不是此表记录 id。
- 您对SQL的尝试使用列 entrytime ，但您没有向我们显示任何数据。如果这只是一个打字错误，那么你确实需要一个包含时间的列 - 但不需要将日期和时间分成单独的列
A few other points:
- Your table employeename2 is not storing employee names - it's storing attendance records and should be named appropriately. Appropriate names would be anything that is not misleading e.g. employeeAttendance, InOutRecord, XY0002349. (The last one is not a good name without a dictionary, but at least it is not misleading).
- The column id on your table is also not clear - it's actually the employee id and not this table record id.
- Your attempt at the SQL uses a column entrytime but you haven't shown us any data for that. If that was just a typing error then you really do need a column that includes the time - but there is no need to separate the date and the time into separate columns
这篇关于如何计算出现次数相同的行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！