mysql - MySQL如何判断一个人是否超过21岁

Question

我正在尝试在 MySQL 中创建一个 View 。我希望它显示用户的电子邮件、名字、出生日期和当前年龄。我只想显示 21 岁以上的用户。

这就是我所拥有的:

CREATE VIEW 'valid_Users' AS 
SELECT Persons.Email AS Email, Persons.FName + ' ' + Persons.LName AS Name,  Persons.DOB AS Birth_Date, DATEDIFF(Persons.DOB, NOW()) AS Current_Age'
FROM Persons
WHERE DATEDIFF(Persons.DOB, NOW()) > 21;

问题是Where子句返回一个负数，这显然不是正确的年龄。我如何编辑这段代码来确定一个人的正确年龄？

我知道这些名称还不起作用......但这是一些当前数据的图像。实际的表是“用户”

Answer 1

尝试:

CREATE VIEW 'valid_Users' AS 
SELECT Persons.Email AS Email, Persons.FName + ' ' + Persons.LName AS Name, Persons.DOB AS Birth_Date, DATE_FORMAT(FROM_DAYS(DATEDIFF(NOW(), Persons.DOB)), "%Y")+0 AS Current_Age'
FROM Persons
WHERE DATE_FORMAT(FROM_DAYS(DATEDIFF(NOW(), Persons.DOB)), "%Y")+0 > 21;

或者:

CREATE VIEW 'valid_Users' AS 
SELECT Persons.Email AS Email, Persons.FName + ' ' + Persons.LName AS Name, Persons.DOB AS Birth_Date, TIMESTAMPDIFF(YEAR, Persons.DOB, NOW()) AS Current_Age'
FROM Persons
WHERE TIMESTAMPDIFF(YEAR, Persons.DOB, NOW()) > 21;