找不到列: 1054‘字段列表’中的未知列'PlantingBlock.id‘

Question

我需要使用cakephp和mysql将字符串传递给存储过程。

下面是我传递给存储过程的字符串，

PlantingBlock.id IN (13,10)

下面是我的存储过程，

SET whereClause = " AND 1 = 1 ";
IF(in_clause IS NULL OR in_clause = '') THEN
    SET whereClause = CONCAT(whereClause, ' AND ',in_clause);
END IF;

SET statement = 'SELECT PlantingBlock.* FROM (
    SELECT PB.*, 
    B.block,
    V.variety,
    ST.size_type,
    PLA.date AS plant_date,
    PLA.week_no,
    C.crop,
    C.id AS crop_id
    FROM lf_planting_blocks AS PB,
         lf_blocks AS B,
         lf_properties AS P,
         lf_property_types AS PT,  
         lf_size_types AS ST,
         lf_varieties AS V,
         lf_plantings AS PLA,
         lf_crops AS C
    WHERE
        PB.block_id = B.id
        AND B.property_id = P.id
        AND P.property_type_id = PT.id
        AND B.size_type_id = ST.id
        AND PB.variety_id = V.id
        AND V.crop_id = C.id
        AND PB.planting_id = PLA.id
        AND PB.plant_still_active = true
        AND B.is_deleted = false
        AND P.is_deleted = false
        AND PT.is_deleted = false
        AND ST.is_deleted = false
        AND V.is_deleted = false
        AND C.is_deleted = false';

SET statement = CONCAT(statement, whereClause, ') AS PlantingBlock ORDER BY PlantingBlock.plant_date DESC');

这会给出一个名为“SQLSTATE42S22: Column not found: 1054 Unknown column‘PlantingBlock.id”in 'field list’的错误

Answer 1

问题是，在使用为该子查询提供的别名引用id列时，您试图向内部select添加一个条件。

因此，您可以使用圆括号结束子查询，并将WHERE子句应用于外部SELECT

SELECT PlantingBlock.* 
FROM 
(
   ...
) AS PlantingBlock
 WHERE PlantingBlock.id IN (13,10) 
 ORDER BY PlantingBlock.plant_date DESC

或者更好的做法是在你的内部select中引用id字段。

SELECT PlantingBlock.* 
FROM 
(
   ...
   AND PB.id IN (13,10)
) AS PlantingBlock
 ORDER BY PlantingBlock.plant_date DESC