问题描述
我有一张表格,其中包含有关参加某些活动的数据.我在表中有用户每次发送新考勤的考勤数据,信息如下:
I have one table with data about attendance into some events. I have in the table the data of the attendance everytime the user sends new attendance, the information is like this:
(这实际上是一个视图,因此一些字段为空).
(This is actually a view, for that reason some of the fields are null).
我可以通过 id_member 分组,因此每个成员只能获得一行(即,只有用户发送的最后一次出席).但是,当我这样做时,我收到了用户发送的第一个出席,而不是最后一个.
I can groupby id_member so I get only one row for every member (that is, only the last attendance the user sent). However, when I do it, I received the first attendance the user sent, not the last one.
我已经尝试添加 ORDER BY 子句,但它们根本不起作用......有什么想法吗?
I already tried to add ORDER BY clausules, but they are not working at all... any ideas?
提前致谢!
编辑:这是创建表格的脚本
编辑 2:
非常感谢 MichaelBenjamin,但是使用子查询时的问题是视图的大小:
Thanks a lot MichaelBenjamin, but the problem when using subqueries is the size of the view:
如您所见,我的表中有很多行,因此我不想使用子查询...
As you can see there are a lot of rows in my table, so for that reason I don't want to use subqueries...
编辑 3:
但是将 WHERE 添加到子查询中看起来更好...
But adding WHERE to the subquery it looks better...
如果我找不到任何不使用子查询的东西,我想我会选择这个作为答案...
If I can not find anything else not using subqueries, I think I'll choose this as the answer...
编辑 4
在看到答案中的评论后,我决定选择另一个作为答案.这是两个查询的 DESCRIBE,我认为最好的解决方案是显而易见的:
After seeing the comments in the answer, I've decided to select another as the answer. Here is the DESCRIBE for both queries, and I think it is obvious what is the best solution:
推荐答案
使用 id_member 的简单组,但选择:
Use a simple group by id_member, but select:
这会将出勤附加到组中每一行的时间戳,以便能够使用 max() 选择所需的时间戳/出勤,然后仅提取出勤.
This attaches attendance to the timestamp for each row in a group, in order to be able to select the desired timestamp/attendance with max() and then extract just the attendance.
concat()
返回的是 19 个字符的格式化时间戳 (YYYY-mm-dd HH:MM:SS),从字符 20 开始附加出勤;substring(... from 20)
仅从该组的(字符串式)最大出席人数中获取出席人数.您可以删除该组,只需
What concat()
returns is 19 characters of formatted timestamp (YYYY-mm-dd HH:MM:SS) with the attendance appended starting at character 20; the substring(... from 20)
gets just the attendance from the (stringwise) maximum one for the group. You can remove the group by and just
更好地了解它如何使用 max 来获得合适的出勤率.
to get a better idea of how it uses max to get the right attendance.
这篇关于在 mysql 中使用 group by 仅选择最后一个值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!