首页 文章

MySQL“Group By”和“Order By”

提问于
浏览
80

我希望能够从电子邮件表中选择一堆行,并通过发件人对它们进行分组 . 我的查询如下所示:

SELECT 
    `timestamp`, `fromEmail`, `subject`
FROM `incomingEmails` 
GROUP BY LOWER(`fromEmail`) 
ORDER BY `timestamp` DESC

查询几乎按我的意愿工作 - 它选择通过电子邮件分组的记录 . 问题是主题和时间戳不对应于特定电子邮件地址的最新记录 .

例如,它可能会返回:

fromEmail: john@example.com, subject: hello
fromEmail: mark@example.com, subject: welcome

当数据库中的记录是:

fromEmail: john@example.com, subject: hello
fromEmail: john@example.com, subject: programming question
fromEmail: mark@example.com, subject: welcome

如果“编程问题”主题是最新的,如何在分组电子邮件时让MySQL选择该记录?

6 回答

  • 21

    一个简单的解决方案是首先使用ORDER语句将查询包装到子选择中,然后再应用GROUP BY:

    SELECT * FROM ( 
        SELECT `timestamp`, `fromEmail`, `subject`
        FROM `incomingEmails` 
        ORDER BY `timestamp` DESC
    ) AS tmp_table GROUP BY LOWER(`fromEmail`)
    

    这类似于使用连接但看起来更好 .

    在带有GROUP BY子句的SELECT中使用非聚合列是非标准的 . MySQL通常会返回它找到的第一行的值,并丢弃其余的行 . 任何ORDER BY子句仅适用于返回的列值,而不适用于丢弃的列值 .

    IMPORTANT UPDATE 选择用于实际工作的非聚合列,但不应依赖它们 . 根据MySQL documentation“这主要是在每个非GROUPAG中未命名的非聚合列中的所有值对每个组都相同时 . 每个组的服务器都是 free to choose any value ,所以 unless they are the same, the values chosen are indeterminate . ”

    从5.6.21开始,我注意到临时表上的GROUP BY问题还原了ORDER BY排序 .

    5.7.5开始,默认情况下启用ONLY_FULL_GROUP_BY,即无法使用非聚合列 .

    http://www.cafewebmaster.com/mysql-order-sort-group https://dev.mysql.com/doc/refman/5.6/en/group-by-handling.html https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

  • 20

    通过使用GROUP BY包装查询,在ORDER BY之后执行GROUP BY:

    SELECT t.* FROM (SELECT * FROM table ORDER BY time DESC) t GROUP BY t.from
    
  • 40

    这是一种方法:

    SELECT cur.textID, cur.fromEmail, cur.subject, 
         cur.timestamp, cur.read
    FROM incomingEmails cur
    LEFT JOIN incomingEmails next
        on cur.fromEmail = next.fromEmail
        and cur.timestamp < next.timestamp
    WHERE next.timestamp is null
    and cur.toUserID = '$userID' 
    ORDER BY LOWER(cur.fromEmail)
    

    基本上,您自己加入表,搜索以后的行 . 在where子句中,您声明不能有以后的行 . 这只给你最新的一行 .

    如果可能有多个具有相同时间戳的电子邮件,则此查询将需要优化 . 如果电子邮件表中有增量ID列,请更改JOIN,如:

    LEFT JOIN incomingEmails next
        on cur.fromEmail = next.fromEmail
        and cur.id < next.id
    
  • 123

    根据SQL标准,您不能在选择列表中使用非聚合列 . MySQL允许这样的使用(使用uless ONLY_FULL_GROUP_BY模式),但结果是不可预测的 .

    ONLY_FULL_GROUP_BY

    您应首先选择fromEmail,MIN(读取),然后选择第二个查询(或子查询) - Subject .

  • 27

    正如已经回答的那样,当前的答案是错误的,因为GROUP BY从窗口中任意选择记录 .

    如果一个人正在使用MySQL 5.6,或者使用带有 ONLY_FULL_GROUP_BY 的MySQL 5.7,那么正确的(确定性的)查询是:

    SELECT incomingEmails.*
      FROM (
        SELECT fromEmail, MAX(timestamp) `timestamp`
        FROM incomingEmails
        GROUP BY fromEmail
      ) filtered_incomingEmails
      JOIN incomingEmails USING (fromEmail, timestamp)
    GROUP BY fromEmail, timestamp
    

    为了使查询有效运行,需要正确的索引 .

    请注意,出于简化目的,我删除了 LOWER() ,在大多数情况下,它将不会被使用 .

  • 2

    对于比这些更复杂的查询,我努力使用这两种方法,因为无论我使用什么索引,子查询方法都非常缺乏,并且因为我无法通过Hibernate获得外部自连接

    执行此操作的最佳(也是最简单)方法是按构造的内容进行分组,以包含所需字段的串联,然后使用SELECT子句中的表达式将它们拉出来 . 如果你需要做一个MAX(),请确保你想要MAX()的字段始终位于连接实体的最重要的一端 .

    理解这一点的关键是,只有当这些其他字段对于满足Max()的任何实体都是不变的时,查询才有意义,因此就排序而言,可以忽略其他部分的连接 . 它解释了如何在此链接的最底部执行此操作 . http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-columns.html

    如果您可以获得插入/更新事件(如触发器)来预先计算字段的串联,则可以将其编入索引,并且查询将如同分组仅仅是您实际想要MAX的字段一样快( ) . 您甚至可以使用它来获取最多的多个字段 . 我用它来对表达为嵌套集的多维树进行查询 .

相关问题