mysql AS 子句

Question

我写了一个查询，从出生日期字段开始计算出一个人的年龄，然后使用 AS age 创建一个年龄字段。

我的问题是，是否可以再次匹配那个年龄字段？

像这样，

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

如有任何帮助，我们将不胜感激？

-----所以我将 WHERE 更改为 HAVING，这是我的完整查询------

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

我得到以下错误，

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

我这辈子都不知道它是什么。

Answer 1

一种方法是将原始查询包装为子查询，并将 WHERE 子句移动到外部查询:

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

---

澄清:这在 MySQL 5 中有效

注意:这假定原始查询有效