时间戳

首页 > 解决方案 > MySQL中的相关子查询无法在主要的stmt中包含“列”

mysql - MySQL中的相关子查询无法在主要的stmt中包含“列”

问题描述

使用以下查询，包括相关的子查询，我希望能够从主查询中找到 PhotoCount （这是子查询）> # 即 > 1 或 > 5 的记录

我试过做

WHERE PhotoCount > # WHERE (SELECT COUNT(operatorID) FROM tbl_photos p WHERE p.operatorID = aoh.operatorID AND p.img_creator = 'Daniël 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

标签： mysqlmariadb

解决方案

如果你想使用你需要使用的列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

推荐阅读