distinct使用笔记！

一条sql语句统计某月去重申请产品记录

需求：导出X月份所有产品申请记录，同一手机号去重

#无去重写法
select productId,p.productName,count(aid) record 
from pjh_action_record r left join pjh_productinfo p on r.productId = p.id
where  r.createTime BETWEEN 1609430400 and 1612108800
group by productId order by record desc

统计记录无去重

#去重子查询作为字段返回
select productId,p.productName,count(aid) record,(
select count(distinct mobile) uniq_record from pjh_action_record 
where productId = r.productId and createTime BETWEEN 1609430400 and 1612108800
) uniq_record
from pjh_action_record r left join pjh_productinfo p on r.productId = p.id
where  r.createTime BETWEEN 1609430400 and 1612108800
group by productId order by record desc

去重子查询作为字段返回

(select count(distinct mobile) uniq_record1 from pjh_action_record 
where productId = r.productId and createTime BETWEEN 1609430400 and 1612108800) uniq_record

1.子查询必须为单一字段，如果多个字段则报错：1241 - Operand should contain 1 column(s)
2.子查询内部字段别名会被外面的别名覆盖：uniq_record会覆盖uniq_record1 
3.去重常用的方法：groupby分组，distinct去重
4.distinct去重使用：
①如果多个字段查询，distinct 字段必须写在最前，否则会报语法错误; select id,distinct mobile ...
②如果是统计去重字段的总数: count(distinct 字段) 则不用放在最前；select id,count(distinct mobile) ...
③如果distinct后跟多个字段，则去重条件是这多个字段的组合有重复。select  distinct mobile m,name n ...

去重子查询作为字段返回

mysql带条件查询多个count实现
用第一种方式会展示多行结果，第二种只会展示一行结果，方便对结果做运算！

用group by分组统计

select id,status,count( DISTINCT id) total from table group by status

id  status total
6000004 1  146244
6000044 2  112036
6000001 3   508

使用count(distinct if(field=x,主键ID,null))方式统计

select id,status,count(DISTINCT if(status=1,id,null)) aa,
count(DISTINCT if(status=2,id,null)) bb,
count(DISTINCT if(status=3,id,null)) cc 
table

aa     bb   cc
171347  63206   410770