PostgreSQL仅支持SELECT语句时，如何根据出生日期计算年龄并统计人数

现有表结构 #

期望查询结果 #

遇到的问题 #

尝试过计算年龄的常见方案，但数据库仅允许执行SELECT语句，禁止任何数据操作，触发如下错误：

DB engine Error
Only SELECT statements are allowed against this database.
This may be triggered by:
Issue 1022 - Database does not allow data manipulation.

出生日期字段为Date & Time类型，格式为DD-MM-YYYY hh:mm。

解决方案 #

以下是不同数据库环境下的纯SELECT查询语句，均不会触发数据操作限制，只需替换YourTableName为实际表名：

SQL Server #

SELECT 
    DATEDIFF(YEAR, [Date of Birth], GETDATE()) 
    - CASE WHEN DATEADD(YEAR, DATEDIFF(YEAR, [Date of Birth], GETDATE()), [Date of Birth]) > GETDATE() THEN 1 ELSE 0 END AS Age,
    COUNT(*) AS [Count]
FROM YourTableName
GROUP BY 
    DATEDIFF(YEAR, [Date of Birth], GETDATE()) 
    - CASE WHEN DATEADD(YEAR, DATEDIFF(YEAR, [Date of Birth], GETDATE()), [Date of Birth]) > GETDATE() THEN 1 ELSE 0 END

MySQL #

SELECT 
    TIMESTAMPDIFF(YEAR, `Date of Birth`, CURDATE()) AS Age,
    COUNT(*) AS `Count`
FROM YourTableName
GROUP BY TIMESTAMPDIFF(YEAR, `Date of Birth`, CURDATE())

PostgreSQL #

SELECT 
    EXTRACT(YEAR FROM AGE(CURRENT_DATE, "Date of Birth"))::INTEGER AS Age,
    COUNT(*) AS "Count"
FROM YourTableName
GROUP BY EXTRACT(YEAR FROM AGE(CURRENT_DATE, "Date of Birth"))::INTEGER

这些语句会准确计算当前年龄（考虑是否已过当年生日），并按年龄分组统计人数，完全符合需求。

内容的提问来源于stack exchange，提问作者user2875230