MySQL 按开始和结束时间进行分组

Question

我在 MySQL 中有一个名为 map_item_group 的表，如下例所示:

item_serial | group_code | start_date | end_date
===================================================
item1       | group1     | 2015-01-01 | 2016-01-01
item1       | group2     | 2016-02-01 | 2016-03-15
item2       | group1     | 2015-06-01 | 2016-06-30
item1       | group2     | 2016-05-18 | 2016-06-30

我想创建一个名为 group_info 的 MySQL View ，如下所示:

group_code | start_date | end_date   | items_string   
=======================================================
group1     | 2015-01-01 | 2015-06-01 | item1    
group1     | 2015-06-01 | 2016-01-01 | item1,item2
group1     | 2016-01-01 | 2016-06-30 | item2   
group2     | 2016-02-01 | 2016-03-15 | item1
group2     | 2016-05-18 | 2016-06-30 | item1        

换句话说，我希望每个组占一行，显示每个时间跨度内该组中的项目。

简单地按 group_code、start_date 和 end_date 进行分组(即 SELECT group_code, start_date, end_date, GROUP_CONCAT(item_serial) FROM map_item_group GROUP BY group_code, start_date, end_date )不会给出所需的输出。

我可以想象使用子查询来做到这一点的方法，但 MySQL View 中不允许使用子查询。作为解决方法，我可以创建其他 View 来代替子查询，但我宁愿避免向我的架构中添加一堆额外的 View 。最干净的方法是什么？

Answer 1

• 首先，我使用 UNION 按 group_code 创建所有日期 (开始 + 结束) 的列表，我称之为 T1 但应该选择不同的名称
• 然后使用变量为每个日期分配 row_number。子查询 T1 和 T2
• 然后必须复制代码以将结果连接到自身并创建范围。子查询R
  • 您可以简化它，使其成为一个单独的 View 。
• 现在我有了范围，请连接回原始表以查看该项目是否属于该范围。

输出

<强> SQL Demo

SELECT R.`group_code`, R.`start_date`, R.`end_date`, GROUP_CONCAT(T.item_serial SEPARATOR ', ') items
FROM (
        SELECT T1.`group_code`, T1.range_date as start_date, T2.range_date as end_date
        FROM (    
                SELECT `group_code`, range_date, 
                        @rn := IF( @grpCode = `group_code`, @rn + 1 , IF(@grpCode := `group_code`, 1, 1)) as rn

                FROM (
                        SELECT `group_code`, `start_date` as range_date
                        FROM Table1
                        UNION
                        SELECT `group_code`, `end_date` as range_date
                        FROM Table1
                        ORDER BY 1, 2
                     ) as T1, 
                     (SELECT @rn := 0, @grpCode := '') r
            ) T1 
        JOIN (
                SELECT `group_code`, range_date, 
                        @rn := IF( @grpCode = `group_code`, @rn + 1 , IF(@grpCode := `group_code`, 1, 1)) as rn

                FROM (
                        SELECT `group_code`, `start_date` as range_date
                        FROM Table1
                        UNION
                        SELECT `group_code`, `end_date` as range_date
                        FROM Table1
                        ORDER BY 1, 2
                     ) as T1, 
                     (SELECT @rn := 0, @grpCode := '') r    
            ) T2
          ON T1.rn = T2.rn -1
         AND T1.group_code = T2.group_code
    ) R
JOIN Table1 T
  ON R.start_date < T.end_date
 AND R.end_date   > T.start_date
 AND R.group_code = T.group_code
GROUP BY R.`group_code`, R.`start_date`, R.`end_date`
ORDER BY 1,2, 4