sql复杂组查询

Question

我有一个包含以下行的表格:

id. type  - link 

 1. image - http://1
 2. image - http://2
 3. text  - none
 4. video - http://..
 5. image - http://..
 6. text  - http://..

我想按日期对类型(图像)进行分组，因此它们显示为单行。在此示例中，前两个图像将合并在一起，输出如下所示:

1. image - http://1, http://2  ** GROUPED BY DATE, if they are same type and not break type after it.
2. text  - none
3. video - http://..
4. image - http://..
5. text  - http://..

Answer 1

SELECT  grouper, type, GROUP_CONCAT(link)
FROM    (
        SELECT  @group := @group + (NOT (COALESCE(@type, type) = type)) AS grouper,
                @type := type,
                m.*
        FROM    (
                SELECT  @group := 0,
                        @type := NULL
                ) vars,
                mytable m
        ) q
GROUP BY
        grouper