计数基于年龄SQL

Question

我有以下的T-SQL来计算年龄：

SELECT 
    Member_ID, 
    DATEDIFF(YY,DOB,GETDATE()) - 
    CASE 
    WHEN DATEADD(YY,DATEDIFF(YY,DOB,GETDATE()),DOB) > GETDATE() THEN 1 
    ELSE 0 
    END AS Age in Years 
FROM MEMBER 
WHERE YEAR(registration_date) >= 2012 

如何计算成员ID的每个年龄段的年数？

Answer 1

事情是这样的：

SELECT 
-- Member_ID, commented out 
DATEDIFF(YY,DOB,GETDATE()) - 
CASE 
WHEN DATEADD(YY,DATEDIFF(YY,DOB,GETDATE()),DOB) > GETDATE() THEN 1 
ELSE 0 
END AS Age in Years 
, count(member_id) membersWithThisAge 
FROM MEMBER 
WHERE YEAR(registration_date) >= 2012 
group by DATEDIFF(YY,DOB,GETDATE()) - 
CASE 
WHEN DATEADD(YY,DATEDIFF(YY,DOB,GETDATE()),DOB) > GETDATE() THEN 1 
ELSE 0 
END 

的member_id不能包括选择子句。如果是这样，那么你会得到每个成员一个年龄的计数。

Answer 2

我会这样做使用子查询或CTE。更容易遵循：

SELECT AgeInYears, COUNT(*) 
FROM (SELECT Member_ID, 
      (DATEDIFF(YY,DOB,GETDATE()) - 
       CASE WHEN DATEADD(YY,DATEDIFF(YY,DOB,GETDATE()),DOB) > GETDATE() THEN 1 
        ELSE 0 
       END) AS AgeinYears 
     FROM MEMBER 
     WHERE YEAR(registration_date) >= 2012 
    ) m 
GROUP BY AgeInYears 
ORDER BY 1;