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<=vMaxAgeYou 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更多推荐
发布评论