group by 去重处理数据

guoyiguang2

已于 2022-04-02 16:26:21 修改

阅读量4.2k

点赞数 1

分类专栏： mysql 文章标签：数据仓库数据库开发

于 2022-03-22 15:21:56 首次发布

本文链接：https://blog.csdn.net/qq_38807606/article/details/123661939

版权

mysql 专栏收录该内容

33 篇文章 0 订阅

订阅专栏




# 需求：结果：
# + ----+------+
# | name|boy_id|
# + ----+------+
# | A   |1     |
# | B   |2     |
# | C   |3     |
# + ----+------+

#  group by 对结果的重复数据去重
select  * from girl ;

# +--+----+------+
# |id|name|boy_id|
# +--+----+------+
# |1 |A   |1     |
# |2 |B   |2     |
# |3 |C   |3     |
# |4 |C   |3     |
# |5 |C   |3     |
# +--+----+------+



select  name ,boy_id  from girl group by name ,boy_id ;

# +----+------+
# |name|boy_id|
# +----+------+
# |A   |1     |
# |B   |2     |
# |C   |3     |
# +----+------+

#

select id ,name ,boy_id from girl ;

# +--+----+------+
# |id|name|boy_id|
# +--+----+------+
# |1 |A   |1     |
# |2 |B   |1     |
# |3 |C   |3     |
# |4 |C   |3     |
# |5 |C   |3     |
# |6 |D   |NULL  |
# |7 |D   |NULL  |
# |8 |C   |2     |
# +--+----+------+

# 如果根据 唯一的一个字段 分组的话，数据量是不变的（比如id，分组原理：id 一样的前提下，才会两条数据合并成一条）

select id ,name ,boy_id from girl  group by id ,name ,boy_id ;
# +--+----+------+
# |id|name|boy_id|
# +--+----+------+
# |1 |A   |1     |
# |2 |B   |1     |
# |3 |C   |3     |
# |4 |C   |3     |
# |5 |C   |3     |
# |6 |D   |NULL  |
# |7 |D   |NULL  |
# |8 |C   |2     |
# +--+----+------+

#  name 和 boy_id  一样的数据
  select  name ,boy_id from girl  group by name ,boy_id ;

# +----+------+
# |name|boy_id|
# +----+------+
# |A   |1     |
# |B   |1     |
# |C   |3     |
# |D   |NULL  |
# |C   |2     |
# +----+------+



# 各个 相同  name 和 boy_id  的数据有几条
select name, boy_id, count(1) as cnt
from girl
group by name, boy_id;

# +----+------+---+
# |name|boy_id|cnt|
# +----+------+---+
# |A   |1     |1  |
# |B   |1     |1  |
# |C   |3     |3  |
# |D   |NULL  |2  |
# |C   |2     |1  |
# +----+------+---+


# 进阶版本：

select  * from boy  ;

# +--+----+--------+
# |id|name|del_flag|
# +--+----+--------+
# |1 |慕容皝 |0       |
# |2 |慕容垂 |0       |
# |3 |慕容博 |1       |
# |4 |慕容复 |1       |
# +--+----+--------+

select  * from girl ;

# +--+----+------+
# |id|name|boy_id|
# +--+----+------+
# |1 |A   |1     |
# |2 |B   |2     |
# |3 |C   |3     |
# |4 |C   |3     |
# |5 |C   |3     |
# |6 |D   |NULL  |
# +--+----+------+

# 需求：  统计每个女生是否真正有男友的情况（假设女生姓名唯一；boy_id 为 3 的男生 已经 被 删掉了，认为 c 没有男友 ），并且去重，结果数据如下：

# +  ----+------+
# |  name|boy_id| flag
# +  ----+------+
# |  A   |1     |  0
# |  B   |2     |  0
# |  C   |3     |  0
# |  D   |NULL  |  0
# +  ----+------+


select name,
       boy_id ,
       case
           when (select count(1) from boy where boy.id  = girl.boy_id and boy.del_flag = 0 ) > 0
               then  1
           else
               0
        end as flag
from girl;
# 结果如下:
# +----+------+----+
# |name|boy_id|flag|
# +----+------+----+
# |A   |1     |1   |
# |B   |2     |1   |
# |C   |3     |0   |
# |C   |3     |0   |
# |C   |3     |0   |
# |D   |NULL  |0   |
# +----+------+----+

#  用户c 的boy_id 都是 3 ，所以分组思路有两个 ① 对元数据进行分组  ② 对 结果数据进行分组

# ① 对元数据进行分组

select name,
       boy_id ,
       case
           when (select count(1) from boy where boy.id  = Tem.boy_id and boy.del_flag = 0 ) > 0
               then  1
           else
               0
        end as flag
from (select  name,boy_id from girl group by name,boy_id ) Tem group by   name ,boy_id ,flag ;

# +----+------+----+
# |name|boy_id|flag|
# +----+------+----+
# |A   |1     |1   |
# |B   |2     |1   |
# |C   |3     |0   |
# |D   |NULL  |0   |
# +----+------+----+




# ② 对 结果数据进行分组

select name,
       boy_id ,
       case
           when (select count(1) from boy where boy.id  = girl.boy_id and boy.del_flag = 0 ) > 0
               then  1
           else
               0
        end as flag
from girl group by   name ,boy_id ,flag ;

# +----+------+----+
# |name|boy_id|flag|
# +----+------+----+
# |A   |1     |1   |
# |B   |2     |1   |
# |C   |3     |0   |
# |D   |NULL  |0   |
# +----+------+----+