Mysql程序givnng错误未知列年龄(Mysql procedure givnng error unknown column Age)

Mysql程序givnng错误未知列年龄(Mysql procedure givnng error unknown column Age) mysql

这是我的Mysql查询

DELIMITER $$ USE `divineproposal_db`$$ DROP PROCEDURE IF EXISTS `pr_Search_Profile`$$ CREATE DEFINER=`root`@`%` PROCEDURE `pr_Search_Profile`(IN vGender CHAR(1),IN vMinAge INT,IN vMaxAge INT,IN vReligion INT) BEGIN SELECT DATE_FORMAT(FROM_DAYS(DATEDIFF(NOW(), cpl_DateofBirth)), "%Y")+0 AS Age FROM tbl_candidateprofile WHERE Age>= vMinAge AND Age<=vMaxAge; END$$ DELIMITER ;

错误::

Unknown column 'Age' in 'where clause'

Here is my Mysql Query

DELIMITER $$ USE `divineproposal_db`$$ DROP PROCEDURE IF EXISTS `pr_Search_Profile`$$ CREATE DEFINER=`root`@`%` PROCEDURE `pr_Search_Profile`(IN vGender CHAR(1),IN vMinAge INT,IN vMaxAge INT,IN vReligion INT) BEGIN SELECT DATE_FORMAT(FROM_DAYS(DATEDIFF(NOW(), cpl_DateofBirth)), "%Y")+0 AS Age FROM tbl_candidateprofile WHERE Age>= vMinAge AND Age<=vMaxAge; END$$ DELIMITER ;

Error ::

Unknown column 'Age' in 'where clause'

最满意答案

您不能在WHERE子句中使用别名，因此您必须使用子查询或重用age的定义：

select Age from ( SELECT DATE_FORMAT(FROM_DAYS(DATEDIFF(NOW(), cpl_DateofBirth)), "%Y")+0 AS Age FROM tbl_candidateprofile ) src WHERE Age>= vMinAge AND Age<=vMaxAge;

要么：

SELECT DATE_FORMAT(FROM_DAYS(DATEDIFF(NOW(), cpl_DateofBirth)), "%Y")+0 AS Age FROM tbl_candidateprofile WHERE DATE_FORMAT(FROM_DAYS(DATEDIFF(NOW(), cpl_DateofBirth)), "%Y")+0>= vMinAge AND DATE_FORMAT(FROM_DAYS(DATEDIFF(NOW(), cpl_DateofBirth)), "%Y")+0<=vMaxAge

You cannot use an alias in a WHERE clause so you will have to either use a subquery or reuse the definition for age:

select Age from ( SELECT DATE_FORMAT(FROM_DAYS(DATEDIFF(NOW(), cpl_DateofBirth)), "%Y")+0 AS Age FROM tbl_candidateprofile ) src WHERE Age>= vMinAge AND Age<=vMaxAge;

Or:

SELECT DATE_FORMAT(FROM_DAYS(DATEDIFF(NOW(), cpl_DateofBirth)), "%Y")+0 AS Age FROM tbl_candidateprofile WHERE DATE_FORMAT(FROM_DAYS(DATEDIFF(NOW(), cpl_DateofBirth)), "%Y")+0>= vMinAge AND DATE_FORMAT(FROM_DAYS(DATEDIFF(NOW(), cpl_DateofBirth)), "%Y")+0<=vMaxAge