MySQL按连续行分组 [英] MySQL Group By Consecutive Rows

问题描述

我有一个提要应用程序，我试图连续对结果进行分组. 我的桌子看起来像这样:

I have a feed application that I am trying to group results from consecutively. My table looks like this:

    postid | posttype | target | action     |        date         | title      | content
      1    |  userid  |  NULL  | upgrade    | 0000-01-00 00:00:00 | Upgraded 1 | exmple
      1    |  userid  |  NULL  | upgrade    | 0000-01-00 00:00:01 | Upgraded 2 | exmple
      1    |  userid  |  NULL  | downgrade  | 0000-01-00 00:00:02 | Downgraded | exmple
      1    |  userid  |  NULL  | upgrade    | 0000-01-00 00:00:03 | Upgraded   | exmple

我希望结果是:

    postid | posttype | target | action     |        date         | title      | content
      1    |  userid  |  NULL  | upgrade    | 0000-01-00 00:00:01 | Upgrade 1  | exmple,exmple
      1    |  userid  |  NULL  | downgrade  | 0000-01-00 00:00:02 | Downgraded | exmple
      1    |  userid  |  NULL  | upgrade    | 0000-01-00 00:00:03 | Upgraded   | exmple

因此您可以看到，因为Upgrade 1&升级2连续发送，将它们分组在一起. 动作"表是参考，应用于连续分组以及postid和&帖子类型.

So as you can see because Upgrade 1 & Upgrade 2 were sent Consecutively, it groups them together. The "Action" table is a reference, and should be used for the consecutive grouping as well as the postid & posttype.

我环顾四周，但没有看到像我一样的东西.预先感谢您的帮助.

I looked around on SO but didnt see anything quite like mine. Thanks in advance for any help.

推荐答案

这是另一个可与MySQL变量一起使用的版本，不需要进行3级嵌套.第一个记录按postID和Date的顺序对记录进行预排序，并且每当Post ID，Type和/或操作之一中的值发生更改时，便为每个组分配一个序列号.从那开始，它是一个简单的小组……不比较记录版本T到T2到T3 ...如果要4或5个条件该怎么办...您是否必须嵌套更多条目?或者仅添加2个@ sql变量进行比较测试...

Here's another version that works with MySQL Variables and doesn't require 3 level nesting deep. The first one pre-sorts the records in order by postID and Date and assigns them a sequential number per group whenever any time a value changes in one of the Post ID, Type and/or action. From that, Its a simple group by... no comparing record version T to T2 to T3... what if you wanted 4 or 5 criteria... would you have to nest even more entries?, or just add 2 more @sql variables to the comparison test...

您的通话效率更高...

Your call on which is more efficient...

select
      PreQuery.postID,
      PreQuery.PostType,
      PreQuery.Target,
      PreQuery.Action,
      PreQuery.Title,
      min( PreQuery.Date ) as FirstActionDate,
      max( PreQuery.Date ) as LastActionDate,
      count(*) as ActionEntries,
      group_concat( PreQuery.content ) as Content
   from
      ( select
              t.*,
              @lastSeq := if( t.action = @lastAction
                          AND t.postID = @lastPostID
                          AND t.postType = @lastPostType, @lastSeq, @lastSeq +1 ) as ActionSeq,
              @lastAction := t.action,
              @lastPostID := t.postID,
              @lastPostType := t.PostType
           from
              t,
              ( select @lastAction := ' ',
                       @lastPostID := 0,
                       @lastPostType := ' ',
                       @lastSeq := 0 ) sqlVars
           order by
              t.postid,
              t.date ) PreQuery
   group by
      PreQuery.postID,
      PreQuery.ActionSeq,
      PreQuery.PostType,
      PreQuery.Action    

这是我的链接到SQLFiddle示例

对于标题，您可能需要调整行...

For the title, you might want to adjust the line...

group_concat(不同的PreQuery.Title)作为标题，

group_concat( distinct PreQuery.Title ) as Titles,

至少这将使DISTINCT标题变得简明...要让其更困难，而又不通过使最大查询日期和其他元素在所有条件下获得与该最大日期相关联的一个标题来使整个查询嵌套一层以上.

At least this will give DISTINCT titles concatinated... much tougher to get let without nesting this entire query one more level by having the max query date and other elements to get the one title associated with that max date per all criteria.

这篇关于MySQL按连续行分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！