存储过程到目前为止工作正常,但我只想在dist_calculated IS NOT NULL时才需要记录.当我在where子句中使用该条件时,它会在’where子句’中显示错误#1054 – 未知列’dist_calculated’. Witout where子句运行良好并返回NULL记录,如：

entity_id   dist_calculated     
49              NULL
50              NULL
52              4460.615
51              4875.179

我想排除NULL.

我试过WHERE dist_calculated IS NOT NULL和WHERE cpe.dist_calculated IS NOT NULL仍然给出错误.

我的存储过程是：

DELIMITER //
CREATE PROCEDURE get_close_childcares(IN latUser DECIMAL(15,6),IN lngUser DECIMAL(15,6) )
BEGIN
    /*Get 4 ids of closest childcares*/
    /*Outer query   
    @param : userLat, userLng, Current childcare lat,current childcare lng
    Note : Inner query returns lat , lng of Current product 176 : lat , 177: lng
    */
    SELECT cpe.entity_id , get_distance_in_miles_between_geo_locations(latUser,lngUser,
        (SELECT cpev.value FROM catalog_product_entity_varchar AS cpev 
            WHERE (cpev.entity_id = cpe.entity_id AND cpev.attribute_id = 176)
        ),
        (SELECT cpev.value FROM catalog_product_entity_varchar AS cpev 
            WHERE (cpev.entity_id = cpe.entity_id AND cpev.attribute_id = 177)
        )
    ) AS dist_calculated
    FROM catalog_product_entity AS cpe
    WHERE dist_calculated IS NOT NULL
    ORDER BY dist_calculated ASC
    LIMIT 0,4;

END
//
DELIMITER ;

并调用存储过程：

致电get_close_childcares(19.992100,73.777000)

谢谢.

解决方法:

MySQL允许在GROUP BY和HAVING上使用列别名,但不允许在WHERE语句中使用.因此,您需要在WHERE中使用完整定义,例如

SELECT cpe.entity_id , get_distance_in_miles_between_geo_locations(latUser,lngUser,
    (SELECT cpev.value FROM catalog_product_entity_varchar AS cpev 
        WHERE (cpev.entity_id = cpe.entity_id AND cpev.attribute_id = 176)
    ),
    (SELECT cpev.value FROM catalog_product_entity_varchar AS cpev 
        WHERE (cpev.entity_id = cpe.entity_id AND cpev.attribute_id = 177)
    )
) AS dist_calculated
FROM catalog_product_entity AS cpe
WHERE
get_distance_in_miles_between_geo_locations(latUser,lngUser,
        (SELECT cpev.value FROM catalog_product_entity_varchar AS cpev 
            WHERE (cpev.entity_id = cpe.entity_id AND cpev.attribute_id = 176)
        ),
        (SELECT cpev.value FROM catalog_product_entity_varchar AS cpev 
            WHERE (cpev.entity_id = cpe.entity_id AND cpev.attribute_id = 177)
        )
    ) IS NOT NULL
ORDER BY dist_calculated ASC
LIMIT 0,4;