在mysql中只使用group by选择最后一个值 [英] Select only last value using group by at mysql
本文介绍了在mysql中只使用group by选择最后一个值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有一张关于参加某些活动的数据表。我在表中列出了每次用户发送新出勤时出席的数据，信息如下：
mysql> SELECT id_branch_channel，id_member，考勤，时间戳，id_member FROM view_event_attendance WHERE id_event = 782;
+ ------------------- + ----------- + ------------ + ------------ + ----------- +
| id_branch_channel | id_member |考勤|时间戳| id_member |
+ ------------------- + ----------- + ------------ + ------------ + ----------- +
| 1326 | 131327 | 459 | 1363208604 | 131327 |
| 1326 | 131327 | 123 | 1363208504 | 131327 |
| 1326 | 131327 | 1 | 1363208459 | 131327 |
| 1326 | 93086 | 0 | NULL | 93086 |
| 1326 | 93087 | 0 | NULL | 93087 |
| 1326 | 93088 | 0 | NULL | 93088 |
| 1326 | 93093 | 0 | NULL | 93093 |
| 1326 | 99113 | 0 | NULL | 99113 |
| 1326 | 99135 | 0 | NULL | 99135 |
| 1326 | 99199 | 0 | NULL | 99199 |
| 1326 | 99200 | 0 | NULL | 99200 |
| 1326 | 131324 | 0 | NULL | 131324 |
| 1326 | 85850 | 0 | NULL | 85850 |
| 1326 | 93085 | 0 | NULL | 93085 |
+ ------------------- + ----------- + ------------ + ------------ + ----------- +
set中的14行（0.00秒）
（这实际上是一个视图，因为这个原因，一些字段为空）。
I可以groupby id_member，所以我只为每个成员（即，只有最后一次出席用户发送）获得一行。但是，当我这样做时，我收到了用户发送的第一个出席者，而不是最后一个。
SELECT id_branch_channel，id_member，attendance，timestamp，id_member FROM view_event_attendance WHERE id_event = 782 GROUP BY id_event，id_member;+ ------------------- + ----------- + ------------ + ------------ + ----------- +
| id_branch_channel | id_member |考勤|时间戳| id_member |
+ ------------------- + ----------- + ------------ + ------------ + ----------- +
| 1326 | 131327 | 1 | 1363208459 | 131327 |
| 1326 | 93086 | 0 | NULL | 93086 |
| 1326 | 131324 | 0 | NULL | 131324 |
| 1326 | 93087 | 0 | NULL | 93087 |
| 1326 | 93088 | 0 | NULL | 93088 |
| 1326 | 93093 | 0 | NULL | 93093 |
| 1326 | 99113 | 0 | NULL | 99113 |
| 1326 | 99135 | 0 | NULL | 99135 |
| 1326 | 85850 | 0 | NULL | 85850 |
| 1326 | 99199 | 0 | NULL | 99199 |
| 1326 | 93085 | 0 | NULL | 93085 |
| 1326 | 99200 | 0 | NULL | 99200 |
+ ------------------- + ----------- + ------------ + ------------ + ----------- +
集合中的12行（0.00秒）
我已经尝试添加ORDER BY clausules，但它们根本无法工作......任何想法？
编辑：这是创建表的脚本
CREATE OR REPLACE VIEW view_event_attendance
AS
SELECT
tbl_event.id_event，
tbl_member_event.id_member，
tbl_event。
tbl_member_event_attendance.id_member_event_attendance，
IF（ISNULL（tbl_member_event_attendance.attendance），0，tbl_member_event_attendance.attendance）AS考勤，
tbl_member_event_attendance.timestamp
FROM
tbl_event
INNER JOIN
tbl_member_event ON tbl_member_event.id_event = tbl_event.id_even t
LEFT OUTER JOIN
tbl_member_event_attend ON ON tbl_member_event_attendance.id_member_event = tbl_member_event.id_member_event
ORDER BY
tbl_member_event_attendance.timestamp DESC;
编辑2：
感谢MichaelBenjamin，但使用子查询时的问题是视图的大小：
mysql> DESCRIBE SELECT id_branch_channel，id_member，考勤，时间戳，id_member
- > FROM（select * from view_event_attendance order by timestamp desc）as any
- > WHERE id_event = 782
- > GROUP BY id_event，id_member;
+ ---- + ------------- + -------------------------- --- + -------- + ----------------- + ----------------- + - -------- + ----------------------------------------- ------- ------- + + ---------------------------------- ------------ +
| id | select_type |表| |键入| possible_keys |键| key_len | ref |行|额外|
+ ---- + ------------- + -------------------------- --- + -------- + ----------------- + ----------------- + - -------- + ----------------------------------------- ------- ------- + + ---------------------------------- ------------ +
| 1 | PRIMARY | < Derived2的> | ALL | NULL | NULL | NULL | NULL | 16755 |在哪里使用;使用临时;使用filesort |
| 2 | DERIVED | tbl_member_event | index | id_event | id_event | 8 | NULL | 16346 |使用索引;使用临时;使用filesort |
| 2 | DERIVED | tbl_event | eq_ref | PRIMARY | PRIMARY | 4 | video_staging.tbl_member_event.id_event | 1 | |
| 2 | DERIVED | tbl_member_event_attendance | ref | id_event_member | id_event_member | 4 | video_staging.tbl_member_event.id_member_event | 1 |使用index |
+ ---- + ------------- + -------------------------- --- + -------- + ----------------- + ----------------- + - -------- + ----------------------------------------- ------- ------- + + ---------------------------------- ------------ +
4行（0.08秒）
正如你所看到的，我的表中有很多行，所以出于这个原因我不想使用子查询...
编辑3：
但是将WHERE添加到子查询中看起来更好...
的MySQL> DESCRIBE SELECT id_branch_channel，id_member，考勤，时间戳，id_member
- > FROM（select * from view_event_attendance where id_event = 782 order by timestamp desc）as any
- > WHERE id_event = 782
- > GROUP BY id_event，id_member;
+ ---- + ------------- + -------------------------- --- + ------- + ----------------- + ----------------- + - ------- + ------------------------------------------ ------ + ------ + ------------------------------------ ---------- +
| id | select_type |表| |键入| possible_keys |键| key_len | ref |行|额外|
+ ---- + ------------- + -------------------------- --- + ------- + ----------------- + ----------------- + - ------- + ------------------------------------------ ------ + ------ + ------------------------------------ ---------- +
| 1 | PRIMARY | < Derived2的> | ALL | NULL | NULL | NULL | NULL | 14 |在哪里使用;使用临时;使用filesort |
| 2 | DERIVED | tbl_event | const | PRIMARY | PRIMARY | 4 | | 1 |使用临时;使用filesort |
| 2 | DERIVED | tbl_member_event | ref | id_event | id_event | 4 | | 12 |使用index |
| 2 | DERIVED | tbl_member_event_attendance | ref | id_event_member | id_event_member | 4 | video_staging.tbl_member_event.id_member_event | 1 |使用index |
+ ---- + ------------- + -------------------------- --- + ------- + ----------------- + ----------------- + - ------- + ------------------------------------------ ------ + ------ + ------------------------------------ ---------- +
4行（0.01秒）
<如果我找不到其他的东西没有使用子查询，我想我会选择这个作为答案...
编辑4
在看到答案中的评论后，我决定选择另一个作为答案。这是两个查询的DESCRIBE，我认为很明显什么是最好的解决方案：
mysql> DESCRIBE SELECT
- > id_branch_channel，
- > id_member，
- >考勤，
- >时间戳，
- > id_member
- > FROM view_event_attendance AS t1
- > WHERE id_event = 782
- > AND timestamp =（SELECT MAX（timestamp）
- > FROM view_event_attendance AS t2
- > WHERE t1.id_member = t2.id_member
- > AND t1.id_event = t2.id_event
- > GROUP BY id_event，id_member）
- > OR时间戳IS NULL
- > GROUP BY id_event，id_member;
+ ---- + -------------------- + ------------------- ---------- + -------- + -------------------- + --------- ----------------- + --------- + ---------------------- -------------------------- + ------ + ---------------- ------------------------------------------- +
| id | select_type |表| |键入| possible_keys |键| key_len | ref |行|额外|
+ ---- + -------------------- + ------------------- ---------- + -------- + -------------------- + --------- ----------------- + --------- + ---------------------- -------------------------- + ------ + ---------------- ------------------------------------------- +
| 1 | PRIMARY | tbl_event | index | PRIMARY | id_member_branch_channel | 4 | NULL | 208 |使用索引;使用临时;使用filesort |
| 1 | PRIMARY | tbl_member_event | ref | id_event | id_event | 4 | video_staging.tbl_event.id_event | 64 |使用index |
| 1 | PRIMARY | tbl_member_event_attendance | ref | id_event_member | id_event_member | 4 | video_staging.tbl_member_event.id_member_event | 1 |在哪里使用;使用index |
| 2 |依赖子查询| tbl_event | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 |在哪里使用;使用索引;使用临时;使用filesort |
| 2 |依赖子查询| tbl_member_event | eq_ref | id_event，id_member | id_event | 8 | video_staging.tbl_event.id_event，func | 1 |在哪里使用;使用index |
| 2 |依赖子查询| tbl_member_event_attendance | ref | id_event_member | id_event_member | 4 | video_staging.tbl_member_event.id_member_event | 1 |在哪里使用;使用index |
+ ---- + -------------------- + ------------------- ---------- + -------- + -------------------- + --------- ----------------- + --------- + ---------------------- -------------------------- + ------ + ---------------- ------------------------------------------- +
6行（0.00秒）
mysql> DESCRIBE SELECT *
- > FROM（SELECT id_branch_channel，id_member，attendance，timestamp，id_event
- > FROM view_event_attendance
- > WHERE id_event = 782
- > ORDER BY timestamp desc
- > ）无论
- > GROUP BY id_event，id_member;
+ ---- + ------------- + -------------------------- --- + ------- + ----------------- + ----------------- + - ------- + ------------------------------------------ ------ + ------ + --------------------------------- +
| id | select_type |表| |键入| possible_keys |键| key_len | ref |行|额外|
+ ---- + ------------- + -------------------------- --- + ------- + ----------------- + ----------------- + - ------- + ------------------------------------------ ------ + ------ + --------------------------------- +
| 1 | PRIMARY | < Derived2的> | ALL | NULL | NULL | NULL | NULL | 14 |使用临时;使用filesort |
| 2 | DERIVED | tbl_event | const | PRIMARY | PRIMARY | 4 | | 1 |使用临时;使用filesort |
| 2 | DERIVED | tbl_member_event | ref | id_event | id_event | 4 | | 12 |使用index |
| 2 | DERIVED | tbl_member_event_attendance | ref | id_event_member | id_event_member | 4 | video_staging.tbl_member_event.id_member_event | 1 |使用index |
+ ---- + ------------- + -------------------------- --- + ------- + ----------------- + ----------------- + - ------- + ------------------------------------------ ------ + ------ + --------------------------------- +
4行（0.00秒）
解决方案
一个由id_member组成的简单组，但选择：
substring（max（concat（from_unixtime（timestamp），attendance）from 20）作为出席人数
这会考虑组中每行的时间戳，以便能够使用max（）选择所需的时间戳/出勤记录，然后提取出席者名单。
什么 concat（）返回是19个字符的格式化时间戳（YYYY-mm-dd HH：MM：SS），其中考勤时间从20开始; 子串（...从20开始）只会从组的最大（串行）开始出席。您可以删除该团体，只需
select concat（from_unixtime（timestamp），attendance），timestamp，attendance
可以更好地了解它如何使用max来获得正确的出席。
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:
mysql> SELECT id_branch_channel, id_member, attendance, timestamp, id_member FROM view_event_attendance WHERE id_event = 782;
+-------------------+-----------+------------+------------+-----------+
| id_branch_channel | id_member | attendance | timestamp | id_member |
+-------------------+-----------+------------+------------+-----------+
| 1326 | 131327 | 459 | 1363208604 | 131327 |
| 1326 | 131327 | 123 | 1363208504 | 131327 |
| 1326 | 131327 | 1 | 1363208459 | 131327 |
| 1326 | 93086 | 0 | NULL | 93086 |
| 1326 | 93087 | 0 | NULL | 93087 |
| 1326 | 93088 | 0 | NULL | 93088 |
| 1326 | 93093 | 0 | NULL | 93093 |
| 1326 | 99113 | 0 | NULL | 99113 |
| 1326 | 99135 | 0 | NULL | 99135 |
| 1326 | 99199 | 0 | NULL | 99199 |
| 1326 | 99200 | 0 | NULL | 99200 |
| 1326 | 131324 | 0 | NULL | 131324 |
| 1326 | 85850 | 0 | NULL | 85850 |
| 1326 | 93085 | 0 | NULL | 93085 |
+-------------------+-----------+------------+------------+-----------+
14 rows in set (0.00 sec)
(This is actually a view, for that reason some of the fields are null).
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.
mysql> SELECT id_branch_channel, id_member, attendance, timestamp, id_member FROM view_event_attendance WHERE id_event = 782 GROUP BY id_event,id_member;
+-------------------+-----------+------------+------------+-----------+
| id_branch_channel | id_member | attendance | timestamp | id_member |
+-------------------+-----------+------------+------------+-----------+
| 1326 | 131327 | 1 | 1363208459 | 131327 |
| 1326 | 93086 | 0 | NULL | 93086 |
| 1326 | 131324 | 0 | NULL | 131324 |
| 1326 | 93087 | 0 | NULL | 93087 |
| 1326 | 93088 | 0 | NULL | 93088 |
| 1326 | 93093 | 0 | NULL | 93093 |
| 1326 | 99113 | 0 | NULL | 99113 |
| 1326 | 99135 | 0 | NULL | 99135 |
| 1326 | 85850 | 0 | NULL | 85850 |
| 1326 | 99199 | 0 | NULL | 99199 |
| 1326 | 93085 | 0 | NULL | 93085 |
| 1326 | 99200 | 0 | NULL | 99200 |
+-------------------+-----------+------------+------------+-----------+
12 rows in set (0.00 sec)
I already tried to add ORDER BY clausules, but they are not working at all... any ideas?
Thanks in advance!
Edit: this is the script that creates the table
CREATE OR REPLACE VIEW view_event_attendance
AS
SELECT
tbl_event.id_event,
tbl_member_event.id_member,
tbl_event.id_branch_channel,
tbl_member_event_attendance.id_member_event_attendance,
IF(ISNULL(tbl_member_event_attendance.attendance), 0, tbl_member_event_attendance.attendance) AS attendance,
tbl_member_event_attendance.timestamp
FROM
tbl_event
INNER JOIN
tbl_member_event ON tbl_member_event.id_event = tbl_event.id_event
LEFT OUTER JOIN
tbl_member_event_attendance ON tbl_member_event_attendance.id_member_event = tbl_member_event.id_member_event
ORDER BY
tbl_member_event_attendance.timestamp DESC;
EDIT 2:
Thanks a lot MichaelBenjamin, but the problem when using subqueries is the size of the view:
mysql> DESCRIBE SELECT id_branch_channel, id_member, attendance, timestamp, id_member
-> FROM (select * from view_event_attendance order by timestamp desc) as whatever
-> WHERE id_event = 782
-> GROUP BY id_event,id_member;
+----+-------------+-----------------------------+--------+-----------------+-----------------+---------+------------------------------------------------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------------------+--------+-----------------+-----------------+---------+------------------------------------------------+-------+----------------------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 16755 | Using where; Using temporary; Using filesort |
| 2 | DERIVED | tbl_member_event | index | id_event | id_event | 8 | NULL | 16346 | Using index; Using temporary; Using filesort |
| 2 | DERIVED | tbl_event | eq_ref | PRIMARY | PRIMARY | 4 | video_staging.tbl_member_event.id_event | 1 | |
| 2 | DERIVED | tbl_member_event_attendance | ref | id_event_member | id_event_member | 4 | video_staging.tbl_member_event.id_member_event | 1 | Using index |
+----+-------------+-----------------------------+--------+-----------------+-----------------+---------+------------------------------------------------+-------+----------------------------------------------+
4 rows in set (0.08 sec)
As you can see there are a lot of rows in my table, so for that reason I don't want to use subqueries...
EDIT 3:
But adding WHERE to the subquery it looks better...
mysql> DESCRIBE SELECT id_branch_channel, id_member, attendance, timestamp, id_member
-> FROM (select * from view_event_attendance where id_event = 782 order by timestamp desc) as whatever
-> WHERE id_event = 782
-> GROUP BY id_event,id_member;
+----+-------------+-----------------------------+-------+-----------------+-----------------+---------+------------------------------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------------------+-------+-----------------+-----------------+---------+------------------------------------------------+------+----------------------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 14 | Using where; Using temporary; Using filesort |
| 2 | DERIVED | tbl_event | const | PRIMARY | PRIMARY | 4 | | 1 | Using temporary; Using filesort |
| 2 | DERIVED | tbl_member_event | ref | id_event | id_event | 4 | | 12 | Using index |
| 2 | DERIVED | tbl_member_event_attendance | ref | id_event_member | id_event_member | 4 | video_staging.tbl_member_event.id_member_event | 1 | Using index |
+----+-------------+-----------------------------+-------+-----------------+-----------------+---------+------------------------------------------------+------+----------------------------------------------+
4 rows in set (0.01 sec)
If I can not find anything else not using subqueries, I think I'll choose this as the answer...
Edit 4
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:
mysql> DESCRIBE SELECT
-> id_branch_channel,
-> id_member,
-> attendance,
-> timestamp,
-> id_member
-> FROM view_event_attendance AS t1
-> WHERE id_event = 782
-> AND timestamp = (SELECT MAX(timestamp)
-> FROM view_event_attendance AS t2
-> WHERE t1.id_member = t2.id_member
-> AND t1.id_event = t2.id_event
-> GROUP BY id_event, id_member)
-> OR timestamp IS NULL
-> GROUP BY id_event, id_member;
+----+--------------------+-----------------------------+--------+--------------------+--------------------------+---------+------------------------------------------------+------+-----------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-----------------------------+--------+--------------------+--------------------------+---------+------------------------------------------------+------+-----------------------------------------------------------+
| 1 | PRIMARY | tbl_event | index | PRIMARY | id_member_branch_channel | 4 | NULL | 208 | Using index; Using temporary; Using filesort |
| 1 | PRIMARY | tbl_member_event | ref | id_event | id_event | 4 | video_staging.tbl_event.id_event | 64 | Using index |
| 1 | PRIMARY | tbl_member_event_attendance | ref | id_event_member | id_event_member | 4 | video_staging.tbl_member_event.id_member_event | 1 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | tbl_event | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | Using where; Using index; Using temporary; Using filesort |
| 2 | DEPENDENT SUBQUERY | tbl_member_event | eq_ref | id_event,id_member | id_event | 8 | video_staging.tbl_event.id_event,func | 1 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | tbl_member_event_attendance | ref | id_event_member | id_event_member | 4 | video_staging.tbl_member_event.id_member_event | 1 | Using where; Using index |
+----+--------------------+-----------------------------+--------+--------------------+--------------------------+---------+------------------------------------------------+------+-----------------------------------------------------------+
6 rows in set (0.00 sec)
mysql> DESCRIBE SELECT *
-> FROM (SELECT id_branch_channel, id_member, attendance, timestamp, id_event
-> FROM view_event_attendance
-> WHERE id_event = 782
-> ORDER BY timestamp desc
-> ) as whatever
-> GROUP BY id_event,id_member;
+----+-------------+-----------------------------+-------+-----------------+-----------------+---------+------------------------------------------------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------------------+-------+-----------------+-----------------+---------+------------------------------------------------+------+---------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 14 | Using temporary; Using filesort |
| 2 | DERIVED | tbl_event | const | PRIMARY | PRIMARY | 4 | | 1 | Using temporary; Using filesort |
| 2 | DERIVED | tbl_member_event | ref | id_event | id_event | 4 | | 12 | Using index |
| 2 | DERIVED | tbl_member_event_attendance | ref | id_event_member | id_event_member | 4 | video_staging.tbl_member_event.id_member_event | 1 | Using index |
+----+-------------+-----------------------------+-------+-----------------+-----------------+---------+------------------------------------------------+------+---------------------------------+
4 rows in set (0.00 sec)
解决方案
Use a simple group by id_member, but select:
substring(max(concat(from_unixtime(timestamp),attendance)) from 20) as attendance
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.
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
select concat(from_unixtime(timestamp),attendance), timestamp, attendance
to get a better idea of how it uses max to get the right attendance.
这篇关于在mysql中只使用group by选择最后一个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
