问题 在mysql中使用group by仅选择最后一个值


我有一张表格，其中包含有关参加某些活动的数据。每次用户发送新的出勤时，我都会在表中列出出勤的数据，信息如下：

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)

（这实际上是一个视图，因此某些字段为空）。

我可以通过id_member进行分组，这样每个成员只能得到一行（也就是说，只有用户发送的最后一次出席）。但是，当我这样做时，我收到了用户发送的第一次出席，而不是最后一次出席。

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)

我已经尝试添加ORDER BY clausules，但它们根本不起作用......任何想法？

提前致谢！

编辑：这是创建表的脚本

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;

编辑2：

非常感谢MichaelBenjamin，但使用子查询时的问题是视图的大小：

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)

正如您所看到的，我的表中有很多行，因此我不想使用子查询...

编辑3：

但是在子查询中添加WHERE看起来更好......

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)

如果我找不到任何其他不使用子查询的内容，我想我会选择这个作为答案......

编辑4

在看到答案中的评论之后，我决定选择另一个作为答案。这是两个查询的DESCRIBE，我认为最明显的解决方案是什么：

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)

2129
2018-03-14 22:44


起源
我相信您可以通过在其中一个分组列上使用max（）或类似函数来完成您正在尝试的操作。我现在没有设置类似的表来测试。 - Eric
您可以检查我的答案，找到一个没有子查询或连接的解决方案。 - JodyT
我有兴趣看到实际的时间;描述差异是我所期望的，并不会让我认为一个人显然更好。如果你也试试我的答案会很好:) - ysth
@ysth我希望，但我不知道怎么做...你能澄清一下吗？也许你可以在这里做到： sqlfiddle.com/#!2/5a6e7/1 - Cito
好： sqlfiddle.com/#!2/af2a1/1 - ysth

答案:

使用id_member的简单组，但选择：

substring(max(concat(from_unixtime(timestamp),attendance)) from 20) as attendance

这会将出勤率添加到组中每一行的时间戳，以便能够使用max（）选择所需的时间戳/出勤率，然后仅提取出勤率。

什么 concat() 返回是格式化时间戳（YYYY-mm-dd HH：MM：SS）的19个字符，附加的出勤率从字符20开始;该 substring(... from 20) 获得该组的（字符串）最大值的出勤率。您可以只删除该组

select concat(from_unixtime(timestamp),attendance), timestamp, attendance

更好地了解它如何使用max来获得正确的出勤率。


7
2018-03-14 23:29


你能用完整的查询更新你的答案吗？我稍后会加上解释。谢谢你的帮助。还有，你能解释一下原因吗？ from 20？ - Cito
我宁愿只提供要选择的表达式，因为原始查询不需要进行其他更改;不得不浏览完整的查询将使其他人更难以在他们的查询中找到并使用这个想法。 - ysth
非常聪明，并且比子查询和连接方法快得多 - Brad Mace
使用它太难看了，除了它确实快得多 - ysth

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;

编辑： 这可能会产生更好的性能：

SELECT *
FROM (SELECT id_branch_channel, id_member, attendance, timestamp, id_member 
      FROM view_event_attendance 
      WHERE id_event = 782 
      ORDER BY timestamp desc
     ) as whatever
GROUP BY id_event,id_member;

只要结果集适合Innodb_buffer_pool，您就不会看到显着的性能下降。


3
2018-03-14 22:56


检查我的评论...谢谢你的帮助:) - Cito
从内部查询中仅选择您需要的内容并将其移动到哪里可以为您提供更好的性能。 - Michael Benjamin

我看到了答案 JOINS 和 Subquerys，但我相信一个简单的 HAVING 子句应该做的伎俩：

SELECT 
  id_branch_channel,
  id_member, 
  attendance, 
  timestamp,
  id_member
FROM view_event_attendance
WHERE id_event = 782 
GROUP BY id_event, id_member
HAVING MAX(timestamp) OR timestamp IS NULL;

编辑： 如果您还想包含这些行，请添加IS NULL检查。

编辑2： 当你已经将它过滤到1个事件时，是否需要按id_event进行分组？

编辑3： 不知道为什么要downvote， 这个sql小提琴 表明它有效。

编辑4： 我要道歉，@ ysth是正确的，SQL Fiddle无法正常工作。我当之无愧的-1，但是当你投票时至少解释为什么我也可以自己学点东西。

以下工作，但不幸的是它再次有一个子查询，并不会比这里发布的其他解决方案更好。 

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;

2
2018-03-14 23:08


为什么这会被贬低？ - JodyT
我明天会测试它，让你知道它是否符合我的需要。如果是，那么这将是答案。谢谢你的帮助。 - Cito
这实际上有效，我更喜欢这个解决方案。我认为数据库的成本更低，我真的需要在这方面做出改进。非常感谢大家的帮助。我已经知道如何使用子查询来完成它，但我不太喜欢它，所以出于这个原因，我选择这个来解决我的问题。 - Cito
你能解释它是如何工作的吗？ （因为我觉得你完全误解了什么 having max(timestamp) 的确） - ysth
修改第三个时间戳会打破它： sqlfiddle.com/#!2/5a6e7/1 - ysth

SUBSTRING_INDEX（SUBSTRING_INDEX（group_concat（％requiredfield％），'，'，count（*）），'，'， - 1）

这将从任何group_concat中获取“required字段”的最后一个值，如果未排序，它将默认为表中的最后一个值。

可以使用group_concat_ws来考虑可能的空字段。


2
2018-03-06 14:51



这是一个选项（未经测试）：

SELECT v.id_branch_channel, v.id_member, v.attendance, v.timestamp, v.id_member 
FROM view_event_attendance v
    JOIN (
        SELECT id_event, id_member, MAX(attendance) maxattendance
        FROM view_event_attendance 
        GROUP BY id_event, id_member ) m ON 
            v.id_event = m.id_event AND
            v.id_member = m.id_member AND
            v.attendance = m.maxattendance
WHERE v.id_event = 782 
GROUP BY v.id_member;

这个概念是为了获得 MAX() 时间戳和使用该字段 JOIN 在你的观点。您可能不需要所有字段 - 实际上取决于您的表结构。但这应该让你朝着正确的方向前进。


1
2018-03-14 22:55


它很棒！但唯一的问题是只返回一行......我希望我给出的示例中的所有12行但第一行，而不是出勤率为1的行，出勤率为459的行。感谢您的帮助。我会检查你的代码，看看我是否可以修改它对我有利（虽然我希望不做子查询......） - Cito
@AbrahamSustaita - 我可以想到的唯一不使用子查询的其他选项是在查询中使用用户定义的变量。但是，我认为你的性能没有太大提升 - 事实上，它可能会更慢。我没有完全理解你的问题 - 用MAX（出勤率）替换MAX（时间戳），你应该得到459（显然也更新你的JOIN）...... - sgeddes
@sgeddes：我相信他希望从时间戳最大的行中出席，即使时间戳较低。 - ysth

一种方法是使用窗口函数和子查询，如果您将选择列表中的条目添加为 row_number() over (partition by id_member order by timestamp desc) 这将解析为按每个id_member组中分组的时间戳（其中1是最旧的）排序行的数字（如果这没有意义，则运行它，它将很清楚）。然后，您可以从中选择额外列= 1的子查询，该列仅选择每个组中具有最高时间戳的行。


-1
2018-03-14 22:51


MySQL不支持Row_Number（）等窗口函数... - sgeddes
谢谢你的帮助......但我真的不知道你的意思是什么......对不起。你能给我一个我添加的查询的例子吗？非常感谢您愿意帮助我！ :) - Cito

热门问题

不使用eval / new函数的JavaScript模板库 当涉及内部类时，Java继承如何工作 .NET Windows服务的奇怪问题 在.ipa或.app下查找App ID 快速入门XSLT参考[关闭] 如何找出Android应用程序中未使用的资源 Ruby中并发的同步方法[重复] 将std :: chrono :: system_clock :: time_point转换为struct timeval并返回 Google Drive API V3（javascript）更新文件内容 Bootstrap 3.0 - 将元素推到底部 受密码保护的.NET ClickOnce部署？ 如何用postgresql安装wordpress coq Set或Type如何成为命题 硒滚动元素进入（中心）视图 在Spring Transaction JUnit测试中自动装配Hibernate会话的正确方法 Git的Dockerfile策略 如何在FOS_PICKFOLDER中使用IFileDialog，同时仍在对话框中显示文件名 在Firefox扩展中复制Google Chrome浏览器操作弹出效果 CakePHP找到MAX 芹菜 - 完成任务的召唤功能 从使用fmemopen创建的流中读取宽字符 .NET是否为每个程序集创建一个字符串实习池？ DefaultModelBinder不绑定嵌套模型 Navigator.MediaDevices.getUserMedia（）使用了哪些相机通信标准？ 选择命名空间名称时应该知道什么？ cout Swagger Codegen CLI Java客户端 - 如何正确使用它 一个很好的哈希函数用于采访整数，字符串？ Maven 3 ciManagement配置的目的是什么？ 如何通过语言文化获取代码页？
licensed under cc by-sa 3.0 with attribution.
隐私政策 往来