MySQL의 상관된 하위 쿼리에서 메인 where stmt에 "column"을 포함할 수 없음

MySQL의 상관된 하위 쿼리에서 메인 where stmt에 "column"을 포함할 수 없음

다음 쿼리를 사용하여 상관된 하위 쿼리를 포함하기 위해 PhotoCount(하위 쿼리)가 > #, 즉 > 1 또는 >5인 메인 쿼리에서 레코드를 찾을 수 있으면 합니다.

나는 해보았습니다.

WHERE 포토카운트 > # WHERE (SELECT COUNT(오퍼레이터)ID) from tbl_photowhere p.operator아이디 = aoh.june아이디 AND p.img_cnonk = 'Daniële E. Cronk') > #

그러나 이 모든 "알 수 없는 열" 오류가 발생합니다.

@PhotoCount:=에 추가한 다음 WHERE PhotoCount를 시도했지만 결과가 없습니다.

SELECT af.frameID, af.msn AS afMSN, af.ln AS afLN, af.aircraft_type AS toestelA, af.aircraft_cat AS afAircraftCat, af.airframe_status, aoh.operatorID, aoh.aircraftframeID, aoh.msn, aoh.ln, aoh.lvmID, aoh.toestel, aoh.aircraft_cat, aoh.reg, aoh.delivered, aoh.delivery_comment, aoh.engines, aoh.configuration, aoh.fleet_number, aoh.aircraft_name, aoh.special_colors, aoh.comments, aoh.exit_date, aoh.last_updated, lvm.luchtvaartmaatschappijID AS lvmid, lvm.luchtvaartmaatschappij AS lvmnaam, lvm.sm_logo, (SELECT COUNT(operatorID) FROM tbl_photos p WHERE p.operatorID =  aoh.operatorID AND p.img_creator = 'Daniël E. Cronk') AS PhotoCount

            FROM tbl_aircraft_frame af

            LEFT JOIN tbl_aircraft_operator_history aoh
            ON af.msn = aoh.msn AND af.aircraft_cat = aoh.aircraft_cat

            LEFT JOIN tbl_toestel t
            ON af.aircraft_cat = t.toestelID

            LEFT JOIN tbl_luchtvaartmaatschappij lvm
            ON aoh.lvmID = lvm.luchtvaartmaatschappijID

다음의 열을 사용하려면SELECT당신은 사용해야 합니다.HAVING대신에WHERE

SELECT 
    af.frameID,
    af.msn AS afMSN,
    af.ln AS afLN,
    af.aircraft_type AS toestelA,
    af.aircraft_cat AS afAircraftCat,
    af.airframe_status,
    aoh.operatorID,
    aoh.aircraftframeID,
    aoh.msn,
    aoh.ln,
    aoh.lvmID,
    aoh.toestel,
    aoh.aircraft_cat,
    aoh.reg,
    aoh.delivered,
    aoh.delivery_comment,
    aoh.engines,
    aoh.configuration,
    aoh.fleet_number,
    aoh.aircraft_name,
    aoh.special_colors,
    aoh.comments,
    aoh.exit_date,
    aoh.last_updated,
    lvm.luchtvaartmaatschappijID AS lvmid,
    lvm.luchtvaartmaatschappij AS lvmnaam,
    lvm.sm_logo,
    (SELECT 
            COUNT(operatorID)
        FROM
            tbl_photos p
        WHERE
            p.operatorID = aoh.operatorID
                AND p.img_creator = 'Daniël E. Cronk') AS PhotoCount
FROM
    tbl_aircraft_frame af
        LEFT JOIN
    tbl_aircraft_operator_history aoh ON af.msn = aoh.msn
        AND af.aircraft_cat = aoh.aircraft_cat
        LEFT JOIN
    tbl_toestel t ON af.aircraft_cat = t.toestelID
        LEFT JOIN
    tbl_luchtvaartmaatschappij lvm ON aoh.lvmID = lvm.luchtvaartmaatschappijID
HAVING PhotoCount > 10

언급URL : https://stackoverflow.com/questions/65012554/correlated-subquery-in-mysql-unable-to-include-column-in-main-where-stmt