mysql - 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(日期) .

screenshot for my query and result

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 |
+-------+------+