假设您每年都有交货,我将其视为JOIN
和汇总:
with years as (
select distinct year(deliverydate) as yyyy
from vehicle
)
select y.yyyy, (y.yyyy - year(v.deliverydate)) as age , count(*)
from vehicle v join
years y
on y.yyyy >= year(v.deliverydate)
group by y.yyyy, (y.yyyy - year(v.deliverydate))
order by y.yyyy, (y.yyyy - year(v.deliverydate));
如果您每年没有交货,则可以明确列出它们:
select y.yyyy, (y.yyyy - year(v.deliverydate)) as age , count(*)
from vehicle v join
(values (2010), (2011), (2012), (2013)) y(yyyy)
on y.yyyy >= year(v.deliverydate)
group by y.yyyy, (y.yyyy - year(v.deliverydate))
order by y.yyyy, (y.yyyy - year(v.deliverydate));
这是db <>小提琴。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句