主页 / dba / 问题 / 101307
Accepted
Timon
Asked: 2015-05-13 08:29:57 +0800 CST

具有时间类型的一张表上的 MySQL 交集

  • 772

我有一个包含 4 列的表：id (int)、name (varchar)、timefrom (time)、timeuntil (time)

现在我想要所有的组合，它们在 timefrom 到 timeuntil 上有一个交集。

我目前的解决方案是我加载所有记录并在 php 中循环它每整整一个小时......但这会花费我很多性能。

任何想法，这有可能在选择中解决吗？

我想知道哪些存在共享相同的 timefrom, timeuntil 例如：

#1 / whole day / 06:00:00 / 18:00:00 
#2 / morning   / 07:00:00 / 12:00:00 
#3 / afternoon / 13:00:00 / 18:00:00

我想得到结果：

#1, #2, #3   (the whole day shares the times with morning, afternoon) 
#2, #1       (the morning shares the times with the whole day) 
#3, #1       (the afternoon shares the times with the whole day)
mysql time

1 个回答

  1. Best Answer

    看来您需要一个具有非相等条件的自连接来检查重叠范围。输出可以是不同的格式。根据您的需要，最简单的是 2 列输出：

    SELECT a.id AS a_id, b.id AS b_id
FROM calendar AS a
  JOIN calendar AS b
    ON  a.id <> b.id
    AND a.timefrom < b.timeuntil
    AND b.timefrom < a.timeuntil 
ORDER BY a.id, b.id ;

    输出将是（2 列）：

    #1    #2   (the whole day shares the times with morning) 
#1    #3   (the whole day shares the times with afternoon) 
#2    #1   (the morning shares the times with the whole day) 
#3    #1   (the afternoon shares the times with the whole day)

    要合并行，您可以使用GROUP_CONCAT()：

    SELECT a.id AS a_id, 
       GROUP_CONCAT(b.id ORDER BY b.id SEPARATOR ' ') AS b_ids
FROM calendar AS a
  JOIN calendar AS b
    ON  a.id <> b.id
    AND a.timefrom < b.timeuntil
    AND b.timefrom < a.timeuntil 
GROUP BY a.id
ORDER BY a.id ;

    输出（2 列）：

    #1    #2 #3   (the whole day shares the times with morning, afternoon) 
#2    #1      (the morning shares the times with the whole day) 
#3    #1      (the afternoon shares the times with the whole day)

    如果您希望每行一行a.id和多列，则需要动态 SQL。

    • 2

相关问题