mysql AS子句 [英] mysql AS clause

问题描述

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

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?

类似这样的东西

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?

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

-----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

我收到以下错误消息，

您的SQL语法有错误；检查与您的MySQL服务器版本相对应的手册，以获取在'HAVING DATE_FORMAT(NOW()，'％​​Y')-DATE_FORMAT(candidates.DOB，'％Y')-(DATE_'附近使用的正确语法在第15行

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.

推荐答案

一种方法是将原始查询包装为子查询，然后将WHERE子句移至外部查询:

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

---

说明:这确实适用于MySQL 5

---

clarification: this DOES work in MySQL 5

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

note: this assumes that the original query works

这篇关于mysql AS子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！