表数据
备注:
- id: 任务id;
- name: 参与人name;
1:distinct
1.1
-- 根据任务ID去重
SELECT DISTINCT id FROM test;
1.2
-- 任务总数
SELECT COUNT(DISTINCT id) FROM test;
1.3
distinct 通常效率较低。它不适合用来展示去重后具体的值,一般与 count 配合用来计算条数
。
distinct 使用中,放在 select 后边,对后面所有的字段的值统一进行去重。比如distinct后面有两个字段,那么 11,11 和 11, 21 这两条记录不是重复值
SELECT DISTINCT id, name FROM test;
2: group by
2.1
SELECT id, name, count(*) FROM test
GROUP BY id;
-- 任务总数
SELECT
count( tmp.id )
FROM
( SELECT id, NAME FROM test GROUP BY id ) tmp
3:row_number
row_number 是窗口函数,语法如下:
row_number() over (partition by <用于分组的字段名> order by <用于组内排序的字段名>) 其中partition by 部分可省略
SELECT
id,
name,
ROW_NUMBER() over (ORDER BY id) rn
FROM test
SELECT
id,
name,
ROW_NUMBER() over (PARTITION by id ORDER BY id) rn
FROM test
SELECT
COUNT( CASE WHEN rn = 1 THEN id ELSE NULL END ) count
FROM
( SELECT id, NAME, ROW_NUMBER() over ( PARTITION BY id ORDER BY id ) rn FROM test ) tmp
更多推荐
MYSQL去重方法汇总
发布评论