根据年龄过滤成员时出现错误代码：1064

Question

我想要获取年龄在这两个值之间的人（假设我想过滤掉年龄在 18 到 24 岁之间的人）。

这是成员表结构：

 member_id 
 member_firstname 
 member_dob(datatype is string) (values like 1987-09-08,1976-09-08,.....)

我使用 mysql 作为我的数据库......

SELECT members.member_Id,
               members.member_Lastname as 'Last Name',
               members.member_Firstname as 'First Name',
               members.member_Postcode as 'Post Code',
               members.member_Reference as Reference,
               members.member_CardNum as 'Card Number',
               members.member_IsBiometric as Biometric,
               members.member_DOB as DoB,
               mshiptypes.mshipType_Name as Membership,
               mshipstatustypes.mshipStatusType_Name as Status,
               membertomships.memberToMship_EndDate as Expiry
                 FROM members
INNER JOIN membertomships ON membertomships.member_Id = members.member_Id
 INNER JOIN mshipoptions ON mshipoptions.mshipOption_Id = membertomships.mshipOption_Id
INNER JOIN mshiptypes ON mshiptypes.mshipType_Id = mshipoptions.mshipType_Id
INNER JOIN mshipstatustypes ON mshipStatusTypes.mshipStatusType_Id = membertomships.mshipStatusType_Id
WHERE memberToMship_EndDate BETWEEN '2011-09-24' AND '2011-09-30'
AND members.member_active LIKE 'y%'
AND (YEAR(CURDATE(members.member_Dob))-YEAR()) - (RIGHT(CURDATE(),5)< RIGHT(member_Dob,5)) BETWEEN '21' AND '102'
ORDER BY members.member_Lastname

出现这样的错误......

     Error Code: 1064
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 'member_Dob))-YEAR()) - (RIGHT(CURDATE(),5)< RIGHT(member_Dob,5)) BETWEEN '21' AN' at line 19

修改：

               SELECT members.member_Id,
                   members.member_Lastname as 'Last Name',
                   members.member_Firstname as 'First Name',
                   members.member_Postcode as 'Post Code',
                   members.member_Reference as Reference,
                   members.member_CardNum as 'Card Number',
                   members.member_IsBiometric as Biometric,
                   members.member_DOB as DoB,
                   mshiptypes.mshipType_Name as Membership,
                   mshipstatustypes.mshipStatusType_Name as Status,
                   membertomships.memberToMship_EndDate as Expiry
                     FROM members
    INNER JOIN membertomships ON membertomships.member_Id = members.member_Id
     INNER JOIN mshipoptions ON mshipoptions.mshipOption_Id = membertomships.mshipOption_Id
    INNER JOIN mshiptypes ON mshiptypes.mshipType_Id = mshipoptions.mshipType_Id
    INNER JOIN mshipstatustypes ON mshipStatusTypes.mshipStatusType_Id = membertomships.mshipStatusType_Id
    WHERE memberToMship_EndDate BETWEEN '2011-10-01' AND '2011-10-16'
    AND members.member_active LIKE 'y%'
    AND (YEAR(CURDATE())-YEAR(member_Dob)
 - (RIGHT(CURDATE(),5)<RIGHT(member_Dob,5)) BETWEEN '18' AND '109'
    ORDER BY members.member_Lastname




  error:


 **

Error Code: 1064
        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 'ORDER BY members.member_Lastname' at line 21

**

Answer 1

1. YEAR() 接受一个 date 参数
2. CURDATE() 不带任何参数。

将该 AND 子句更改为：

AND (YEAR(CURDATE())-YEAR(STR_TO_DATE(member_Dob, '%Y-%m-%d'))
     - (RIGHT(CURDATE(),5)<RIGHT(member_Dob,5))) BETWEEN 21 AND 102

请注意，您可能不需要 STR_TO_DATE 如果您将其存储为 YYYY-MM-DD 但为了安全起见，我无论如何都会使用它。下面的条件也应该有效：

AND (YEAR(CURDATE())-YEAR(member_Dob)
     - (RIGHT(CURDATE(),5)<RIGHT(member_Dob,5))) BETWEEN 21 AND 102