如何计算出现次数相同的行？ [英] How can I count the rows with same type occurrences?

问题描述

例如，我想计算一天中员工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

推荐答案

听起来你真的想要通过entrytype为每个条目分区的行号，员工ID和条目日期（不是进入时间）。



这在MSSQL中是微不足道的，但在MySQL中则不是那么多。您正在寻找的概念是窗口功能。



这是关于它们的MySQL文档 MySQL :: MySQL 8.0参考手册:: 12.20.2窗口函数概念和语法 [ ^ ]



这是一个来自Kenneth Xu的工作实例 Program它：MySQL中的分析功能 - ROW_NUMBER，RANK，DENSE_RANK [ ^ ]



如果有帮助，MSSQL相当于你尝试做的事情是

It sounds like you actually want the row number for each entry partitioned by the entrytype, the employee id and the entrydate (not the entrytime).

This is trivial in MSSQL but not so much in MySQL. The concept you are looking for is "Window" functions.

This is the MySQL documentation on them MySQL :: MySQL 8.0 Reference Manual :: 12.20.2 Window Function Concepts and Syntax[^]

And here is a worked example from Kenneth Xu Program It: Analytical Function in MySQL - ROW_NUMBER, RANK, DENSE_RANK[^]

If it helps, the MSSQL equivalent of what you are tryng to do is

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

这篇关于如何计算出现次数相同的行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！