在SQL (SQLite)中，我有一列小学生的名字和他们的生日--我如何向最年长的5名学生展示？

Question

在SQL (SQLite)中，我有一列名字和他们的生日--我如何向最年长的5个人展示？

在一个单独的命令中，我如何使用生日并按年龄分组：

21-30
31-40
41-50
51+

非常感谢

Z

Answer 1

展示最年长的5个人

SELECT * FROM tableName
ORDER BY birth_date DESC LIMIT 5

为了分组生日，我使用了here的答案来计算年龄

SELECT person_name,
       CASE WHEN age BETWEEN 21 AND 30 THEN '21-30'
            WHEN age BETWEEN 31 AND 40 THEN '31-40'
            WHEN age BETWEEN 31 AND 40 THEN '31-40'
            WHEN age >=51 THEN '51+' END as Age_Group
FROM
(
   SELECT person_name,
       (strftime('%Y', 'now') - strftime('%Y', Birth_Date)) - (strftime('%m-%d', 'now') < strftime('%m-%d', Birth_Date));
   FROM tableName as age

) As Z   

查找属于每个出生组的生日数

SELECT Age_Group , COUNT(*) as countAgeGroup
FROM (
       SELECT 
           CASE WHEN age BETWEEN 21 AND 30 THEN '21-30'
                WHEN age BETWEEN 31 AND 40 THEN '31-40'
                WHEN age BETWEEN 31 AND 40 THEN '31-40'
                WHEN age >=51 THEN '51+' END as Age_Group
       FROM
       (
          SELECT 
             (strftime('%Y', 'now') - strftime('%Y', Birth_Date)) - (strftime('%m-%d', 'now') < strftime('%m-%d', Birth_Date));
          FROM tableName as age

       ) As Z  
   ) As Y
  GROUP BY Y.Age_Group