mysql group by first_在mysql中只使用group by选择最后一个值

最新推荐文章于 2024-07-14 03:46:14 发布

weixin_39853968

最新推荐文章于 2024-07-14 03:46:14 发布

阅读量493

点赞数

文章标签： mysql group by first

本文链接：https://blog.csdn.net/weixin_39853968/article/details/113208531

版权

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;

+-------------------+-----------+------------+------------+-----------+

+-------------------+-----------+------------+------------+-----------+

| 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;

+-------------------+-----------+------------+------------+-----------+

+-------------------+-----------+------------+------------+-----------+

| 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

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;

+----+-------------+-----------------------------+--------+-----------------+-----------------+---------+------------------------------------------------+-------+----------------------------------------------+

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;

+----+-------------+-----------------------------+-------+-----------------+-----------------+---------+------------------------------------------------+------+----------------------------------------------+

| 2 | DERIVED | tbl_member_event | ref | id_event | id_event | 4 | | 12 | 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;

+----+--------------------+-----------------------------+--------+--------------------+--------------------------+---------+------------------------------------------------+------+-----------------------------------------------------------+

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;

+----+-------------+-----------------------------+-------+-----------------+-----------------+---------+------------------------------------------------+------+---------------------------------+

+----+-------------+-----------------------------+-------+-----------------+-----------------+---------+------------------------------------------------+------+---------------------------------+

| 2 | DERIVED | tbl_member_event | ref | id_event | id_event | 4 | | 12 | 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.

weixin_39853968

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫