mysql as子句为表取名_mysql AS子句

I have written a query, and from the date of birth field, and I working out the age of a person and then using AS age to create an age field.

My question is, is it possible to the match again that age field?

Something like this,

SELECT `candidates`.`candidate_id`,

`candidates`.`first_name`,

`candidates`.`surname`,

`candidates`.`DOB`,

`candidates`.`gender`,

DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(`candidates`.`DOB`, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(`candidates`.`DOB`, '00-%m-%d')) AS `age`

FROM `candidates`

WHERE `age` <= 20

Any help would be greatly appreciated?

-----So I have changed WHERE to HAVING and here is my full query------

SELECT `candidates`.`candidate_id`,

`candidates`.`first_name`,

`candidates`.`surname`,

`candidates`.`DOB`,

`candidates`.`gender`,

`candidates`.`talent`,

`candidates`.`location`,

`candidates`.`availability`,

`candidate_assets`.`url`,

`candidate_assets`.`asset_size`

FROM `candidates`

LEFT JOIN `candidate_assets` ON `candidate_assets`.`candidates_candidate_id` = `C`.`candidate_id`

WHERE `C`.`availability` = 'yes'

AND C.talent = "actor"

AND C.skill = "accents"

AND C.gender = "male"

AND HAVING DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(`candidates`.`DOB`, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(`candidates`.`DOB`, '00-%m-%d')) <= 69

AND HAVING DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(`candidates`.`DOB`, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(`candidates`.`DOB`, '00-%m-%d')) <=19

AND HAVING DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(`candidates`.`DOB`, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(`candidates`.`DOB`, '00-%m-%d')) <=49

I get the following error,

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'HAVING DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(candidates.DOB, '%Y') - (DATE_' at line 15

For the life of me I have no idea what it is.

解决方案

One way to do this is to wrap the original query as a subquery, and move the WHERE clause to the outer query:

SELECT * FROM (

SELECT `candidates`.`candidate_id`, -- this is the original query

`candidates`.`first_name`,

`candidates`.`surname`,

`candidates`.`DOB`,

`candidates`.`gender`,

DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(`candidates`.`DOB`, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(`candidates`.`DOB`, '00-%m-%d')) AS `age`

FROM `candidates` ) as innertable

WHERE `age` <= 20 -- this is now part of the outer query

clarification: this DOES work in MySQL 5

note: this assumes that the original query works