mysql - 如何将此 SQL 语句转换为 PostgreSQL？

Question

我有一个无法在 PostgreSQL 服务器上运行的 MySQL 语句。

这是原始的 MySQL 查询:

SELECT count(*) AS howmany 
FROM members 
WHERE member_status =1 
AND member_sex = 'male' 
AND (YEAR( '2015-12-31' ) - YEAR( member_birthdate ) ) - 
    ( RIGHT( '2015-12-31', 5 ) < RIGHT( member_birthdate, 5 ) ) 
BETWEEN 27 AND 40;

这是我的方法:

SELECT COUNT(*) AS howmany FROM members
WHERE member_status =1 
AND member_sex ='male'
AND (EXTRACT(YEAR FROM '2015-12-31'::date) - EXTRACT(YEAR FROM member_birthdate)) 
BETWEEN 27 AND 40;

我的目标是，我想知道在 2015 年 12 月 31 日合格日期有多少成员年龄在 27 到 40 岁之间。我不知道如何转换查询的正确部分。

Answer 1

您可以使用 AGE功能:

SELECT COUNT(*) AS howmany 
FROM members
WHERE member_status =1 
  AND member_sex ='male'
  AND extract(year from age(timestamp '2015-12-31', member_birthdate))
      BETWEEN 27 AND 40;