您可以使用subselect来准备值，然后对它们进行分析和分组：

select case 
when elapsed is null then 'missing'
when elapsed <= 1095 then 'new'
when elapsed <= 3650 then 'middle'
else 'old'
end as period, count(*)
from
(select datediff(now(), yourdate) period from yourtable) t
group by period

http://sqlfiddle.com/#!9/86b630/2号

SELECT IF(my_date IS NULL, 'NO DATE', 
          IF(YEAR(NOW())-YEAR(my_date)>10,'>10', 
            IF(YEAR(NOW())-YEAR(my_date)>3,'3-10','<3')
          )
         ) `period`, COUNT(id)
FROM contacts
GROUP BY `period`

您需要在group子句中使用case语句：

SELECT  CASE 
            WHEN floor(DATEDIFF(EndDate, StartDate)/365) < 3 THEN '<3'
            WHEN floor(DATEDIFF(EndDate, StartDate)/365) < 10 THEN '3-10'
            WHEN floor(DATEDIFF(EndDate, StartDate)/365) = 10 THEN '=10'
            ELSE '>10' 
        END AS `Length`
FROM    ...
WHERE   ...
GROUP   BY  CASE 
                WHEN floor(DATEDIFF(EndDate, StartDate)/365) < 3 THEN '<3'
                WHEN floor(DATEDIFF(EndDate, StartDate)/365) < 10 THEN '3-10'
                WHEN floor(DATEDIFF(EndDate, StartDate)/365) = 10 THEN '=10'
                ELSE '>10' 
            END
ORDER   BY Length ASC;

如果你的结束日期是现在，就用 NOW() .