这些查询可以帮助我们了解重复程度,但是,却无法告诉我们重复的是哪些值。为了弄清楚表employees中哪些名称是复制的,我们可以使用下列查询来显示非唯一值,以及重复次数:
SELECT dept_id,
name,
count(name) as name_count
FROM employees
GROUP BY name,
dept_id;
name,
count(name) as name_count
FROM employees
GROUP BY name,
dept_id;
我们这里仅对重复数据感兴趣,所以使用HAVING子句将其他数据全部过滤掉:
SELECT dept_id,
name,
count(name) as name_count
FROM employees
GROUP BY name,
dept_id
HAVING name_count > 1;
name,
count(name) as name_count
FROM employees
GROUP BY name,
dept_id
HAVING name_count > 1;