mysql查询去重总结

错误做法：

distinct 去重 ， distinct去重只能去重单个字段
group by 去重, group by 是用来分组的，不能用于去重
1. 分组查询中选用非分组列：SELECT后面出现的非分组列一定是直接依赖分组列，并且在每个组内的值是唯一的。可以在服务器的SQL_MODE设置为包含ONLY_FULL_GROUP_BY来禁止这样的写法。

1
2


SELECT @@sql_mode ;
-- 如果是 only_full_group_by 是没办法用 group by 去重的

通用做法

使用自连接查询【不需要依赖于 @@sql_mode 的设置】

参考博客

参考知乎博客，窗口函数

总结技巧

in (max)
join equal (max)
group by ，需要设置 @@sql_mode，不通用，建议别用
自定义rank变量【 https://zhuanlan.zhihu.com/p/101310541】
窗口函数，暂时没资料

用自连接查询

场景说明

answer表	字段说明
answer_id	用户填写的答案id
paper_id	试卷id【有多份试卷，用户可以重复答题】
content	用户作答的内容

answer 表是用户答案记录表

使用 in

1
2
3


	
-- paper_id 相同，取 answer_id 最大的作为 结果	
select a.*  from t_answer 	a   where a.answer_id in (select max(answer_id ) from t_answer group by paper_id )

使用 join

1
2
3
4
5


 
-- paper_id 相同，取 answer_id 最大的作为 结果	
select a.*  from t_answer 	a    join (select max(answer_id) answer_id  from t_answer group by paper_id  ) atemp 

on a.answer_id = atemp.answer_id

自定义变量

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20


-- 用 rank 变量计数， 数到第一个的就出来
set @cur = -1;
SELECT
	a.* 
FROM
	(
	SELECT
		a.*,
		@rank :=
	IF
		( @cur = a.paper_id, @rank + 1, 1 ) AS rk,
		@cur := a.paper_id AS cur_paper_id 
	FROM
		t_answer a 
	ORDER BY
		paper_id,
		answer_id DESC 
	) a 
WHERE
	rk = 1

另一种写法

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16


set @preId = -1;

select * from (
SELECT
	a.* ,
	case 
	when @preId = paper_id then @curRank:= @curRank +1 -- 等于，于是自增 ,  @preId 记录上一个 id
	when @preId := paper_id then @curRank:=1  -- 不等于，于是赋值 ,一开始 @preId is null 
	
	end as rank_no 
	from t_answer a  
 ) p 
 
 where p.rank_no <= 1
 
 order by paper_id ,rank_no 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14


select * from (
SELECT
	  a.* ,
	  @rank := if(@pre = paper_id,@rank+1,1) as rank_no  ,
    @pre:= paper_id

	   from t_answer a ,
		 (select @pre := -1 ) x
		 
     order by a.paper_id ,a.answer_id desc
 ) p
 where p.rank_no <= 1
 
 order by paper_id ,rank_no  

窗口函数

mysql8 才支持窗口函数，这里做一下介绍

1
2
3
4
5
6


select * , rank()  over (
	partition by customer_id  order by amount desc 
) as ranking 


from payment 

每一个顾客为一个窗口，只在窗口内部做ranking操作

使用记录

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19


 select * from (
SELECT
	   a.* ,
		 p.title,
	   @rank := if(@pre = paper_id,@rank+1,1) as rank_no  ,
     @pre:= paper_id
 
	   from t_answer a    join  t_paper p on a.paper_id = p.id 
		 
		 ,(select @pre := -1 ) xxx
		  	order by paper_id,answer_id desc

 ) p 
	
 where p.rank_no <= 1
 
 order by paper_id ,rank_no  
 
   

mysql 8新特性

mysql8 除了窗口函数还有隐藏索引，上面已经介绍了窗口函数，这里介绍隐藏索引

1
2
3


show index from `payment`;
alter table payment alter index fk_payment_rental invisible;
alter talbe payment alter index fk_payment_rental visible ;

降序索引-

8.0 之前只支持升序索引

我们给成绩排名，经常给从高到低排序，升序索引对付这种性能不好，数据库索引甚至用不了。

很多不来能走索引覆盖的语句，升序索引无法覆盖，降序索引解决了这种问题

1
2

create index idx1 on payment (payment_date desc);

其他

[[..\八股文专题【面试八股文】\mysql相关.md]]

文章目录