这是五个字
2018-10-12 06:06
采纳率: 16.7%
浏览 431

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

图片说明

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

图片说明

结果是这种的

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

3条回答 默认 最新

  • Syhemsoc 2018-10-12 07:28
    已采纳

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

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

    点赞 评论
  • 差点九零后 2018-10-12 07:13

    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

    点赞 评论
  • 六月下起了大雪 2018-10-12 08:31

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

    点赞 评论

相关推荐 更多相似问题