现在表中分段记录了一些信息
如 user_id begin end duration type
begin end是起始和截止时间
duration=end-begin 有个最大限制;所以造成了记录上的分段.
type: 有first,middle, last.代表是分段的次序.中间可能有多个middle.
eg:
| bceedabcecdgdcf | 2011-03-02 09:49:57 | 2011-03-02 10:04:57 |900 | first | | bceedabcecdgdcf | 2011-03-02 10:04:57 | 2011-03-02 10:19:57 |900 | middle | | bceedabcecdgdcf | 2011-03-02 10:19:57 | 2011-03-02 10:22:03 |126 | last |
现在相把连续的多行合并成一条记录.如何操作?
如上述变成
| bceedabcecdgdcf | 2011-03-02 09:49:57 | 2011-03-02 10:22:03 |1926| single|
可以使用groupby合并多条记录，SQL如下：
select user_id, min(begin) as begin, max(end) as end, end-begin as duration from table group by user_id;