PHP从所有行MYSQL之间获取日期

2 回答

• 0

  这是你如何选择所有对：

  select t1.ID as t1ID, t2.ID as t2ID, t1.WORK_ID as, datediff(t2.`DATE`, t1.`DATE) as delta
  from Work t1
  join Work t2
  on t1.WORK_ID = t2.WORK_ID and t1.`ID` < t2.`ID`
  

  但是你确定你想要选择所有对而不是彼此连续的对吗？

  编辑

  初始查询选择了所有对，现在我们将看看如何选择连续项：

  select t1.ID as t1ID, t2.ID as t2ID, t1.WORK_ID as, datediff(t2.`DATE`, t1.`DATE) as delta
  from Work t1
  join Work t2
  on t1.WORK_ID = t2.WORK_ID and t1.`ID` < t2.`ID`
  where not exists
      (select 1
       from Work t3
       where t1.`ID` < t3.`ID` and t3.`ID` < t2.`ID` and t1.WORK_ID = t3.WORK_ID)
  

  请注意，查询与初始查询类似，在查找具有类似 WORK_ID 的对，其中t1 . ID <t2 . ID 但我们添加了一个附加条件，即不存在满足某些条件的记录 . 标准是第三条记录的 ID 介于 t1 和 t2 的 ID 之间，并且具有类似的 WORK_ID . 当然，您可以跳过 ID 比较到 date 比较 .

  回复于 2024-04-29T20:40:19+08:00
• 0

  SELECT x.date start_date
       , MIN(y.date) end_date 
    FROM my_table x 
    JOIN my_table y 
      ON y.work_id = x.work_id 
     AND y.date > x.date 
   WHERE x.work_id = 5
   GROUP 
      BY x.date;
  

  ...可以扩展到（未经测试）......

  SELECT x.*
       , 5 * (DATEDIFF(LEAST(x.end_date,LAST_DAY(x.start_date)), x.start_date) DIV 7) + MID('0123444401233334012222340111123400001234000123440', 7 * WEEKDAY(x.start_date) + WEEKDAY(x.end_date) + 1, 1) total 
    FROM 
       (
  SELECT a.date start_date
       , MIN(b.date) end_date 
    FROM my_table a 
    JOIN my_table b 
      ON b.work_id = a.work_id 
     AND b.date > a.date 
   WHERE a.work_id = 5
   GROUP 
      BY a.date
       ) x
  

  回复于 2024-04-29T20:40:19+08:00