다른 테이블에 존재하지 않음으로써 SQL의 특정 행 표시

다른 테이블에 존재하지 않음으로써 SQL의 특정 행 표시

고급 SQL과 스택 오버플로우는 잘 모르기 때문에 필요한 것을 설명하려고 노력하고 있습니다.

'Shift'라는 테이블과 'Schedule'이라는 테이블이 있다고 칩시다.

'Shift'에는 'shift_id, shift_start, shift_end, shift_day, shift_function_id' 열이 있습니다.

shift_start는 시작 시간을 나타냅니다.

shift_end는 종료시간을 나타냅니다.

shift_day는 한 주의 요일 수를 나타냅니다(일요일부터0 ~ 6 ） 。

shift_function_id는 시프트에 속하는 function_id를 나타냅니다.

---

'Schedule'에는 'schedule_id, schedule_date, schedule_start, schedule_end, schedule_function_id' 열이 있습니다.

schedule_date는 일정의 날짜를 나타냅니다.

schedule_start는 시작 시간을 나타냅니다.

schedule_end는 종료시간을 나타냅니다.

schedule_function_id는 스케줄에 속하는 function_id를 나타냅니다.

'Shift'의 행이 특정 날짜의 'Schedule' 테이블에 존재하지 않는 경우where shift_start = schedule_start AND shift_end = schedule_end AND shift_function_id = schedule_function_id에 행을 나타냅니다.

다음은 예를 제시하겠습니다.

SELECT shift_id, shift_day, shift_start, shift_end, function_id, function_name, function_color
FROM Shift
LEFT JOIN Function
ON function_id = shift_function_id
WHERE shift_day = $day
AND NOT EXISTS (
    SELECT 1
    FROM Schedule
    WHERE schedule_date = '$date' AND schedule_start = shift_start AND schedule_end = shift_end AND schedule_function_id = shift_function_id
)
ORDER BY function_name, shift_start, shift_end;

문제는요.

시작 시간과 종료 시간이 같은 2교대가 있고 '스케줄' 테이블에 function_id와 시작 시간과 종료 시간이 같은 행이 하나 포함되어 있으면 2교대가 모두 표시되지 않습니다.

표 내용의 예를 다음에 나타냅니다.

스케쥴

schedule_id: 310
schedule_date: 2020-01-11
schedule_start: 16:30:00
schedule_end: 20:00:00
schedule_function_id: 27

시프트

shift_id: 45
shift_day: 6
shift_start: 16:30:00
shift_end: 20:00:00
shift_function_id: 27

shift_id: 46
shift_day: 6
shift_start: 16:30:00
shift_end: 20:00:00
shift_function_id: 27

'Shift'의 두 행이 더 이상 표시되지 않습니다.

내가 원하는 것

'Schedule'에 'Shift'에 지정된 데이터와 동일한 정보가 포함된 행이 1개만 있으면 다른 행이 나타나길 원합니다.

'스케줄'에 동일한 정보를 가진 행이 2개 있으면 표시되지 않습니다.동일한 정보를 가진 '스케줄' 행의 수에 따라 달라지기만 하면 됩니다.

이미지들

아무것도 입력되지 않은 경우 시작과 끝이 동일한 2개의 행이 표시됩니다.

시작 시간과 종료 시간이 같은 레코드를 넣으면 교대 행이 모두 제거됩니다.

시작 시간과 종료 시간이 같은 하나의 레코드만 채울 때 필요합니다.

제가 생각할 수 있는 유일한 방법은NOT EXISTS절은 모든 행을 제거합니다. 중복된 행에서 MIN(또는 MAX) Shift_id를 사용하여 1개의 행을 추가합니다.

SELECT shift_id, 
       shift_day, 
       shift_start, 
       shift_end, 
       function_id, 
       function_name, 
       function_color 
FROM   shift 
       LEFT JOIN function 
              ON function_id = shift_function_id 
WHERE  shift_day = $day 
       AND NOT EXISTS (SELECT 1 
                       FROM   schedule 
                       WHERE  schedule_date = '$date' 
                              AND schedule_start = shift_start 
                              AND schedule_end = shift_end 
                              AND schedule_function_id = shift_function_id) 
UNION ALL 
SELECT Min(S1.shift_id), 
       S1.shift_day, 
       S1.shift_start, 
       S1.shift_end, 
       function_id, 
       function_name, 
       function_color 
FROM   shift S1 
       JOIN shift S2 
         ON S1.shift_id <> S2.shift_id 
            AND S1.shift_day = S2.shift_day 
            AND S1.shift_start = S2.shift_start 
            AND S1.shift_end = S2.shift_end 
       LEFT JOIN function 
              ON function_id = S1.shift_function_id 
GROUP  BY S1.shift_day, 
          S1.shift_start, 
          S1.shift_end, 
          function_id, 
          function_name, 
          function_color 
ORDER  BY function_name, 
          shift_start, 
          shift_end;

여기 작업 예가 있습니다.

사용 중인 버전의 MariaDB가 창 기능을 지원하는 경우row_number()같은 것을 가진 기록을 명확히 하기 위한 서브쿼리에서(shift_start, shift_end, shift_function) shift_table, 「」(schedule_start, schedule_end, schedule_function) schedule

후 를 사용할 수(이렇게 했습니다).NOT EXITS합니다.LEFT JOIN이것은 본질적으로 같은 논리입니다.)

select s.*, f.*
from (
    select 
        s.*, 
        row_number() over(
            partition by shift_start, shift_end, shift_function 
            order by shift_id
        ) rn
    from shift s
    where shift_day = @shift_day
) s
left join (
    select 
        c.*,
        row_number() over(
            partition by schedule_start, schedule_end, schedule_function 
            order by schedule_id
        ) rn
    from schedule c
    where schedule_date = @schedule_date
) c 
    on  c.schedule_start = s.shift_start 
    and c.schedule_end = s.shift_end
    and c.schedule_function_id = s.shift_function_id
    and c.rn = s.rn
left join function f
    on f.function_id = s.shift_function_id
where c.rn is null
order by f.function_name, s.shift_start, s.shift_end

언급URL : https://stackoverflow.com/questions/59544329/show-specific-rows-in-sql-by-not-existing-in-another-table