获得重复结果mysql查询

Question

这是我的查询

SELECT DISTINCT
    a.emp_id,
    a.shift_id,
    a.shift_start_date,
    DATE((b.shift_start_date) - INTERVAL 1 DAY) AS shift_end_date
FROM
    temp_shift a
        LEFT JOIN
    temp_shift b ON a.emp_id = b.emp_id
        AND a.shift_id != b.shift_id
        AND a.shift_start_date < b.shift_start_date
WHERE
    a.active = 'Y'
GROUP BY a.emp_id , a.shift_id , a.shift_start_date
ORDER BY a.emp_id , a.shift_start_date

我试着根据下一次换班时间来确定换班日期。我的成绩很好。但我的问题是我不希望结果像1，5，2-5，3-5，4-5，5-5，6-7(日期)，应该是1-5，6-7(日期)。

查询和结果的屏幕截图

Answer 1

“但如何解决呢？”

DROP TABLE IF EXISTS t;
CREATE TABLE t(id INT);
INSERT INTO t VALUES(1),(2),(3),(4),(6),(7),(8); 

SELECT a.id start
     , MIN(c.id) end 
  FROM t a
  LEFT 
  JOIN t b 
    ON b.id = a.id - 1
  LEFT 
  JOIN t c 
    ON c.id >= a.id 
  LEFT 
  JOIN t d  
    ON d.id = c.id + 1
 WHERE b.id IS NULL 
   AND c.id IS NOT NULL
   AND d.id IS NULL
 GROUP 
    BY a.id; 
+-------+------+
| start | end  |
+-------+------+
|     1 |    4 |
|     6 |    8 |
+-------+------+