减少表格以避免空间不足 [英] reducing table to avoid space inefficiency

问题描述

我有下表:

 id    study_start_time  study_end_time 
____________________________________________
1234          168              256
2345          175              233
1234          256              300
1234          300              389
2345          400              425
4567          200              225 

ı想获得下表；

  id    study_start_time  study_end_time 
____________________________________________
1234          168              389               

过程；

1234 starts at 168. minutes  , work until to 256. minutes
1234   "    "  256.  "       "  "    "    "  300.    "
1234   "    "  300.  "       "  "    "    "  389.    "

，但实际表应缩小为如下所示

but actual table should be reduced to as shown below

1234 starts at 168. minutes ,  work until to 389. minutes

我该怎么做?

推荐答案

不会因为美而获得奖项，但是可以起作用:

Won't win an award for beauty but works:

SELECT x.c1id       id,
       MIN(c1study_start_time) study_start_time,
       MAX(c2study_end_time)   study_end_time
FROM   (SELECT c1.id    c1id,
               c1.study_start_time c1study_start_time,
               c1.study_end_time   c1study_end_time,
               c2.id    c2id,
               c2.study_start_time c2study_start_time,
               c2.study_end_time   c2study_end_time
        FROM   c c1
               inner join c c2
                 ON c2.study_start_time = c1.study_end_time) x
WHERE  x.c2id
GROUP  BY c1id

UNION ALL

SELECT x.c1id    id,
       x.c1study_start_time study_start_time,
       x.c1study_end_time   study_end_time
FROM   (SELECT c1.id    c1id,
       c1.study_start_time c1study_start_time,
       c1.study_end_time   c1study_end_time,
       c2.id    c2id,
       c2.study_start_time c2study_start_time,
       c2.study_end_time   c2study_end_time
    FROM   c c1
      left outer join c c2
      ON c2.study_start_time = c1.study_end_time) x
WHERE  x.c2id IS NULL
       AND x.c1id NOT IN (SELECT c1id
       FROM   (SELECT c1.id    c1id,



c1.study_start_time c1study_start_time,

c1.study_end_time   c1study_end_time,

c2.id    c2id,

c2.study_start_time c2study_start_time,

c2.study_end_time   c2study_end_time
FROM   c c1

inner join c c2

 ON c2.study_start_time = c1.study_end_time) x

      WHERE  x.c2id
      GROUP  BY c1id); 

联合的第一部分只为您提供没有空缺的课程. 第二部分弥补了差距，并排除了已经接受的课程 在第一部分中.

The first part of the union gets you only the courses with no gaps. The second part gets the gaps and excludes the courses already received in the first part.

这篇关于减少表格以避免空间不足的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！