关于orcale关联查询并分类的问题

假设现在有这么两个表，要做关联查询，查询出每个乡镇每个年龄段的driver有多少个，该怎么写sql语句

结果是这种的

使用的是oracle数据库，mysql基本上一样

SELECT t.tname,z.A1年,z.A2年,z.A3年 FROM town t LEFT JOIN (
SELECT dr.id,SUM(CASE WHEN dr.years>=1 AND dr.years<=5 THEN 1 ELSE 0 END) A1年,SUM(CASE WHEN dr.years>5 AND dr.years<=10 THEN 1 ELSE 0 END) A2年,SUM(CASE WHEN dr.years>10 AND dr.years<=15 THEN 1 ELSE 0 END) A3年 FROM driver dr GROUP BY dr.id
) z ON t.tid=z.id

表截图：

结果截图：

SELECT
town_name,
SUM(
CASE
WHEN driver_years >= 1
AND driver_years < 5 THEN
1
END
) AS '1-5',
SUM(
CASE
WHEN driver_years >= 5
AND driver_years < 10 THEN
1
END
) AS '5-10',
SUM(
CASE
WHEN driver_years >= 10
AND driver_years < 15 THEN
1
END
) AS '10-15'
FROM
(
SELECT
t.town_name,
d.*
FROM
town t
LEFT JOIN driver d ON t.id = d.company_id
) a
GROUP BY
town_name

select x.town_name,z.x1 as "1-5年",z.x2 as "5-10年",z.x3 as "10-15年"
from town x left join (select a.company_id,sum(case when a.driving_years>=1 and a.driving_years<=5 then 1 else 0 end) as x1,
sum(case when a.driving_years>5 and a.driving_years<=10 then 1 else 0 end) as x2,
sum(case when a.driving_years>10 and a.driving_years<=15 then 1 else 0 end) as x3 from driver a group by a.company_id) z
on x.id = z.company_id;