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

图片说明

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

图片说明

结果是这种的

3个回答

使用的是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

表截图:
图片说明
结果截图:
图片说明

qq_38615635
这是五个字 谢谢,已解决!
11 个月之前 回复
emsoc
Syhemsoc A1 A2 A3 换成 "1-5年" "5-10年" "10-15年"
11 个月之前 回复

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

qq_38615635
这是五个字 谢谢,已解决!
11 个月之前 回复

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;
图片说明

qq_38615635
这是五个字 谢谢,已解决!
11 个月之前 回复
Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!