SQL left join去重

                                                SQL 去重

使用group by去重

目的:计算每组的总得分,其中is_quit=1的member的得分不记录在内

  • 对总体结果用group by

members要过滤掉is_quit=1的数据,用group by group_id可以对group的记录去重,也可也计算出总分,实现如下:

select g.group_id, 
g.group_name,
group_concat(m.member_name) member_names , 
sum(s.score) sum_score 
from groups g 
left join members m 
on g.group_id = m.group_id
left join score s 
on s.member_id = m.member_id
where m.is_quit = 0 or m.is_quit is null
group by g.group_id
;
  • 对可能重复的表用group by 但score中一个member有多条记录,因此可以先对members表以group by计算&去重,再和groups表进行left join
select g.group_id, 
g.group_name,m.member_names,
m.sum_score 
from groups g 
left join( 
select group_concat(m1.member_name) as member_names,
m1.group_id,
sum(s.score) sum_score 
from members m1 
left join score s 
on s.member_id = m1.member_id
where m1.is_quit=0 or m1.is_quit is null
group by m1.group_id
) m 
on g.group_id = m.group_id
;

使用distinct去重

目标:列出还有组员的小组

select distinct g.group_id, 
g.group_name 
from groups g 
join members m 
on g.group_id = m.group_id
;

NOTE:distinct用于多个字段时,需要这些字段都相同的时候才会达到滤重的目的。如下,由于member_name不尽相同,因此distinct没有起到想要的效果

select g.group_id, 
g.group_name, 
m.member_name 
from groups g 
join members m 
on g.group_id = m.group_id
;

test:

-- members的创建语句
CREATE TABLE `members` (
    `member_id` INT(11) NOT NULL COMMENT '成员id',
    `member_name` VARCHAR(64) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
    `group_id` INT(11) NULL DEFAULT NULL COMMENT '队id'
)
COMMENT='成员'
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
;

-- groups的创建语句
CREATE TABLE `groups` (
    `group_id` INT(11) NOT NULL COMMENT '队id',
    `group_name` VARCHAR(64) NOT NULL COMMENT '队名称' COLLATE 'utf8mb4_unicode_ci'
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
;

-- score的创建语句
CREATE TABLE `score` (
    `member_id` INT(11) NOT NULL COMMENT '成员id',
    `score` INT(11) NOT NULL COMMENT '得分'
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
;
alter table `members` add column `is_quit` int(11) null comment '已退出1:是;0:否';

insert into members values (1,'小珩',1,0),(2,'小豪',1,null),(3,'小萌',1,null),(4,'阿峰',2,0),(5,'阿易',2,null),(6,'飞儿',3,null),(7,'花儿',3,,null),(8,'魏魏',null,null);
insert into groups values (1,'水之队'),(2,'山之队'),(3,'云之队'),(4,'天之队');
insert into score values (1,0),(2,1),(3,0),(4,0),(5,2),(6,0),(7,1),(2,1),(5,1);

 

已标记关键词 清除标记
相关推荐
©️2020 CSDN 皮肤主题: 技术黑板 设计师:CSDN官方博客 返回首页