# distinct 和 group by有什么区别

### 1、创建测试数据

``````-- 创建测试表
drop table if exists pageview;
create table pageview(
id bigint primary key auto_increment comment '自增主键',
aid bigint not null comment '文章ID',
uid bigint not null comment '（访问）用户ID',
createtime datetime default now() comment '创建时间'
) default charset='utf8mb4';
-- 添加测试数据
insert into pageview(aid,uid) values(1,1);
insert into pageview(aid,uid) values(1,1);
insert into pageview(aid,uid) values(2,1);
insert into pageview(aid,uid) values(2,2);
``````

### 2、distinct使用

distinct基本语法如下：

``````SELECT DISTINCT column_name,column_name FROM table_name;
``````

#### 2.1 单列去重

``````select distinct aid from pageview
``````

#### 2.2 多列去重

``````select distinct aid,uid from pageview
``````

#### 2.3 聚合函数+去重

``````select count(distinct aid) from pageview
``````

### 3 group by使用

group by 基本语法如下：

``````SELECT column_name,column_name FROM table_name
WHERE column_name operator value
GROUP BY column_name
``````

#### 3.1 单列去重

``````select * from pageview group by aid;
``````

#### 3.2 多列去重

``````select * from pageview group by aid,uid;
``````

#### 3.3 聚合函数 + group by

``````select aid,count(aid) from pageview group by aid
``````

### 4 distinct和group by 区别

1、区别1：查询结果集不同

``````select  distinct aid from pageview;
``````

``````select * from pageview group by aid;
``````

2、区别2：使用业务场景不同

``````select aid,count(distinct aid) from pageview;
``````

``````-- 统计分组之后数量大于 2 的文章
select * from pageview group by aid having count(aid) >= 2
``````

3、区别3：性能不同