MySQL根据标准重叠的时间段 [英] MySQL overlaping time periods based on criteria

问题描述

我正在尝试编写一个查询，该查询将向我显示根据值Valid_From和Valid_To以及基于条件重叠的所有记录.

i am trying to write a query which would show me all records which overlap based on values Valid_From and Valid_To and based on criteria.

这是我的示例数据:

ID  |  Valid_From  |  Valid_To  |  Block_Type  |  Valid  |  Block_ID
1      5              10           a              TRUE      1
2      10             15           a              TRUE      1
3      6              10           a              TRUE      1
4      3              4            a              FALSE     1
5      2              4            a              FALSE     1
6      1              1            a              FALSE     1
7      1              10           b              TRUE      1
8      1              10           c              TRUE      2
9      1              10           c              TRUE      2
10     1              15           b              TRUE      1
11     5              10           a              TRUE      1

• ID是具有自动增量的主键-每条记录都是唯一的

  • ID is Primary Key with Auto Increment - unique for every record

    Block_Type-块类型-每个Block_ID示例可以具有更多值:Block_ID 1具有Block_Type"a"和"b"，Block_ID 2具有Block_Type"c"，每个Block_ID可以具有任何Block_Type，意味着每个Block_ID的Block_Type可以从"a"到"z"

    Block_Type - Type of block - can have more values for every Block_ID example: Block_ID 1 has Block_Type "a" and "b", Block_ID 2 has Block_Type "c", every Block_ID can have any Block_Type, meaning every Block_ID can have Block_Type "a" to "z"

    为显示哪些记录重叠，我使用此查询:

    For showing what records overlap i use this query:

    select a.id, GROUP_CONCAT(b.id) as abcd
    from new_table a, new_table b
    where a.id <> b.id
    and a.Block_ID = b.Block_ID
    and a.Block_Type = b.Block_Type
    and a.Valid = b.Valid
    and ((a.Valid_To between b.Valid_From and b.Valid_To) or (a.Valid_From between b.Valid_From and b.Valid_To)
    or (a.Valid_To = b.Valid_From) or (a.Valid_From = b.Valid_To))
    group by a.id;
    

    这工作正常，它告诉我是否有任何具有相同Block_Type，Valid和Block_ID中相同值的记录重叠，我的问题是此查询没有显示不重叠的记录(在本示例中是ID为6的记录)i完全理解原因，因此我尝试使用case而不是where来编辑查询，但到目前为止我仍无法实现我想要的.

    this works fine, it shows me if any records with same Block_Type, same value in Valid and Block_ID overlap, my problem is that this query doesnt show me records which dont overlap (in this example it is record with ID 6) i completely understand why, so i tried to edit my query using case when instead of where but so far i wasnt able to achieve what i want.

    这是我的查询现在返回的内容:

    This is what my query returns now:

    ID  |  abcd
    1      2
    2      1,11,3
    3      2
    4      5
    5      4
    7      10
    8      9
    9      8
    10     7
    11     2
    

    我希望此查询列出所有记录，并在新列"abcd"中列出所有重叠的记录，对于不重叠的记录，我希望在同一列中为null，因此如下所示:

    I would like this query to list all records and to list in new column "abcd" all overlaping records, for records that do not overlap i would just like null in the same column, so it would look like this:

    ID  |  abcd
    1      2
    2      1,11,3
    3      2
    4      5
    5      4
    6      null
    7      10
    8      9
    9      8
    10     7
    11     2
    

    不能让您指出正确的方向吗?我可以进行哪些更改以使其按我的方式工作?

    cant you point me in the right direction? What can i change to make it work the way i need?

    非常感谢您

    推荐答案

    如果我正确理解了您的问题，则需要左连接而不是内部连接-

    If i understand your problem correctly, You need a left join instead of Inner join -

    SELECT a.id, GROUP_CONCAT(b.id) AS abcd
    FROM new_table a
    LEFT JOIN new_table b ON a.id <> b.id
                          AND a.Block_ID = b.Block_ID
                          AND a.Block_Type = b.Block_Type
                          AND a.Valid = b.Valid
                          AND ((a.Valid_To BETWEEN b.Valid_From AND b.Valid_To)
                                OR (a.Valid_From BETWEEN b.Valid_From AND b.Valid_To)
                                OR (a.Valid_To = b.Valid_From)
                                OR (a.Valid_From = b.Valid_To)
                              )
    GROUP BY a.id;
    

    这篇关于MySQL根据标准重叠的时间段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！