1. MySQL group by对单字分组序和多字段分组的方法讲解

     更新时间:2019年03月26日 15:12:49   作者?#22909;?#20811;斯   我要评论

    今天小编就为大家分享一篇关于MySQL group by对单字分组序和多字段分组的方法讲解,小编觉得内容挺不错的,现在分享给大家,具有很好的参考价值,需要的朋友一起跟随小编来看看吧

    我这里创建了一个 goods 表,先看下里面的数据:

    mysql> select * from goods;
    +----+------+------+------------+-------------+------------+
    | id | s_id | b_id | goods_name | goods_price | goods_desc |
    +----+------+------+------------+-------------+------------+
    | 1 |  1 |  5 | book    |    22.35 | book    |
    | 2 |  2 |  5 | ball    |    32.25 | ball    |
    | 3 |  3 |  5 | NULL    |    3.23 | NULL    |
    | 4 |  3 |  5 | macbook  |    3.23 | book    |
    | 5 |  3 |  5 | listbook  |    2.30 | book    |
    | 6 |  1 |  1 | nicebook  |   9999.00 | nicebook  |
    | 7 |  2 |  3 | googlebook |    25.30 | book    |
    +----+------+------+------------+-------------+------------+

    1、根据s_id分组

    mysql> select *,group_concat(goods_name) goods_names,group_concat(goods_desc) goods_descs,group_concat(id) ids,group_concat(goods_price) goods_prices from goods group by s_id;
    +----+------+------+------------+-------------+------------+------------------+---------------+-------+----------------+
    | id | s_id | b_id | goods_name | goods_price | goods_desc | goods_names   | goods_descs  | ids  | goods_prices  |
    +----+------+------+------------+-------------+------------+------------------+---------------+-------+----------------+
    | 1 |  1 |  5 | book    |    22.35 | book    | book,nicebook  | book,nicebook | 1,6  | 22.35,9999.00 |
    | 2 |  2 |  5 | ball    |    32.25 | ball    | ball,googlebook | ball,book   | 2,7  | 32.25,25.30  |
    | 3 |  3 |  5 | NULL    |    3.23 | NULL    | macbook,listbook | book,book   | 3,4,5 | 3.23,3.23,2.30 |
    +----+------+------+------------+-------------+------------+------------------+---------------+-------+----------------+

    这里使用了 group_concat()函数,主要目的是为了显示分组的详细信息

    上面的根据单个字段分组很简单,把相同s_id的记?#32423;?#24402;组了

    2、根据s_id,goods_desc字段分组

    分析:这里查询分组时,会先根据s_id分组,然后对每个组里面的数据再根据goods_desc进行分组

    mysql> select *,group_concat(goods_name) goods_names,group_concat(goods_desc) goods_descs,group_concat(id) ids,group_concat(goods_price) goods_prices from goods group by s_id,goods_desc;
    +----+------+------+------------+-------------+------------+------------------+-------------+------+--------------+
    | id | s_id | b_id | goods_name | goods_price | goods_desc | goods_names   | goods_descs | ids | goods_prices |
    +----+------+------+------------+-------------+------------+------------------+-------------+------+--------------+
    | 1 |  1 |  5 | book    |    22.35 | book    | book       | book    | 1  | 22.35    |
    | 6 |  1 |  1 | nicebook  |   9999.00 | nicebook  | nicebook     | nicebook  | 6  | 9999.00   |
    | 2 |  2 |  5 | ball    |    32.25 | ball    | ball       | ball    | 2  | 32.25    |
    | 7 |  2 |  3 | googlebook |    25.30 | book    | googlebook    | book    | 7  | 25.30    |
    | 3 |  3 |  5 | NULL    |    3.23 | NULL    | NULL       | NULL    | 3  | 3.23     |
    | 4 |  3 |  5 | macbook  |    3.23 | book    | macbook,listbook | book,book  | 4,5 | 3.23,2.30  |
    +----+------+------+------------+-------------+------------+------------------+-------------+------+--------------+

    这里的goods_descs 和 上面的一比较就明白了

    接下来还可以再根据 goods_price 分组

    mysql> select *,group_concat(goods_name) goods_names,group_concat(goods_desc) goods_descs,group_concat(id) ids,group_concat(goods_price) goods_prices from goods group by s_id,goods_desc,goods_price;
    +----+------+------+------------+-------------+------------+-------------+-------------+------+--------------+
    | id | s_id | b_id | goods_name | goods_price | goods_desc | goods_names | goods_descs | ids | goods_prices |
    +----+------+------+------------+-------------+------------+-------------+-------------+------+--------------+
    | 1 |  1 |  5 | book    |    22.35 | book    | book    | book    | 1  | 22.35    |
    | 6 |  1 |  1 | nicebook  |   9999.00 | nicebook  | nicebook  | nicebook  | 6  | 9999.00   |
    | 2 |  2 |  5 | ball    |    32.25 | ball    | ball    | ball    | 2  | 32.25    |
    | 7 |  2 |  3 | googlebook |    25.30 | book    | googlebook | book    | 7  | 25.30    |
    | 3 |  3 |  5 | NULL    |    3.23 | NULL    | NULL    | NULL    | 3  | 3.23     |
    | 5 |  3 |  5 | listbook  |    2.30 | book    | listbook  | book    | 5  | 2.30     |
    | 4 |  3 |  5 | macbook  |    3.23 | book    | macbook   | book    | 4  | 3.23     |
    +----+------+------+------------+-------------+------------+-------------+-------------+------+--------------+

    这里主要是进行多个字段分组的时候,只需掌握分组顺序后面的字段是根据前面字段分组后的内容再进行的分组即可。

    在平时的开发任务中我们经常会用到MYSQL的GROUP BY分组, 用来获取数据表中以分组字段为依据的统计数据。比如有一个学生选课表,表结构如下:

    Table: Subject_Selection

    Subject  Semester  Attendee
    ---------------------------------
    ITB001  1     John
    ITB001  1     Bob
    ITB001  1     Mickey
    ITB001  2     Jenny
    ITB001  2     James
    MKB114  1     John
    MKB114  1     Erica

    我们想统计每门课程有多少个学生报名,应用如下SQL:

    SELECT Subject, Count(*)
    FROM Subject_Selection
    GROUP BY Subject

    得到如下结果:

    Subject    Count
    ------------------------------
    ITB001     5
    MKB114     2

    因为表里记录了有5个学生选择ITB001,2个学生选择了MKB114。

    产生这个结果的原因是:

    GROUP BY X意思是将所有具有相同X字段值的记录放到一个分组里。

    那么GROUP BY X, Y呢?

    GROUP BY X, Y意思是将所有具有相同X字段值和Y字段值的记录放到一个分组里。

    总结

    以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,谢谢大家对脚本之家的支持。如果你想了解更多相关内容请查看下面相关链接

    相关文章

    最新评论

    山东群英会开奖查询
      1. 35选7摇奖器 广酉快乐双彩复式中奖计算 排球女将周晓兰 江苏快三精准大小计划 买彩票被骗17万 篮彩让分胜负二窜一 前中后组三组六走势 搜狐彩票nba 2019年重庆时时彩计划表 极速11选5骗局 体彩p3字谜总汇 龙江风采22选5开奖 蜡笔小新两码中特 体彩e球彩18110562 18090期足彩胜负彩结果