programing

두 개의 선택 테이블을 결합하여 선택 쿼리에 실제 결과를 표시하는 것과 동일하지 않은 이유

lastmoon 2023. 6. 26. 21:36
반응형

두 개의 선택 테이블을 결합하여 선택 쿼리에 실제 결과를 표시하는 것과 동일하지 않은 이유

저는 MySQL을 사용하고 있습니다.!= 조건으로 2개의 쿼리에 참여하려고 합니다.이 예에서는 빈 결과 집합을 반환해야 합니다.하지만 조건이 적용되지 않은 것 같습니다.왜 그럴까요?

제 시도는 다음과 같습니다.

    SELECT today_student.* FROM (
    SELECT scd.student_id, sc.transaction_date
    FROM student_collection_details scd
    INNER JOIN student_collection sc
     ON (scd.student_collection_id = sc.id)
    WHERE 1=1
    AND sc.transaction_date BETWEEN DATE('2022-06-01 00:00:00') AND DATE('2022-06-27 00:00:00')
    AND scd.admission_year_id = 2
    AND scd.month_id = 21
    AND scd.collection_head_id = 9  
    GROUP BY scd.student_id
) prev_student,

(
    SELECT scd.student_id, sc.transaction_date
    FROM student_collection_details scd
    INNER JOIN student_collection sc
     ON (scd.student_collection_id = sc.id)
    WHERE 1=1
    AND sc.transaction_date = DATE('2022-06-28 00:00:00')
    AND scd.admission_year_id = 2
    AND scd.month_id = 21
    AND scd.collection_head_id = 9  
    GROUP BY scd.student_id
) today_student

WHERE 1=1
AND  prev_student.student_id != today_student.student_id

prev_message 반환 수:

    1196; 2022-06-20 00:00:00
    1861; 2022-06-18 00:00:00

today_message 반환:

1196; 2022-06-28 00:00:00
1861; 2022-06-28 00:00:00

사용HAVING최소라는 조건을 가진 조항transaction_date이라'2022-06-28':

SELECT scd.student_id, 
       MIN(sc.transaction_date) transaction_date
FROM student_collection_details scd INNER JOIN student_collection sc
ON scd.student_collection_id = sc.id
WHERE sc.transaction_date BETWEEN '2022-06-01 00:00:00' AND '2022-06-28 00:00:00'
  AND scd.admission_year_id = 2
  AND scd.month_id = 21
  AND scd.collection_head_id = 9
GROUP BY scd.student_id
HAVING MIN(sc.transaction_date) = '2022-06-28 00:00:00';

언급URL : https://stackoverflow.com/questions/72827941/why-not-equal-displaying-actual-result-in-select-queries-by-joining-two-select-t

반응형