liyong9006
liyong9006
采纳率0%
2016-11-03 12:35 阅读 4.4k

MySQL将数据按某一字段的范围分组显示,SQL语句怎么写?

员工表(emp)按工资(sal)分组(3000以下,3000至5000,5000至10000和10000以上),然后统计人数。SQL语句该怎么写?

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

3条回答 默认 最新

  • guwei4037 极简吧 2016-11-03 12:54
    点赞 1 评论 复制链接分享
  • yicp123 yicp123 2016-11-04 03:46

    select
    count(1),
    (case when t.sal when t.sal BETWEEN 3000 and 5000 then "3000~5000"
    when t.sal BETWEEN 5000 and 10000 then "5000~10000"
    when t.sal > 10000 then "大于10000" end) as sal
    from emp t
    group by (case when t.sal when t.sal BETWEEN 3000 and 5000 then "3000~5000"
    when t.sal BETWEEN 5000 and 10000 then "5000~10000"
    when t.sal > 10000 then "大于10000" end);

    参考自:MySQL如何优化GROUP BY http://www.data.5helpyou.com/article237.html
    MySQL控制流程函数 http://www.data.5helpyou.com/article327.html

    点赞 评论 复制链接分享
  • qq_35348330 qq_35348330 2020-11-26 20:13

    select elt(interval(sal,0,3000,5000,10000,10000),"1","2","3","4","5") as sort ,count(*) 
    from emp group by sort;

    点赞 评论 复制链接分享

相关推荐