MySQL中的数据去重，该用DISTINCT还是GROUP BY？

-- 单列去重
SELECT DISTINCT department FROM employees;

-- 多列去重（基于所有指定列的组合）
SELECT DISTINCT department, job_title FROM employees;

-- 单列去重
SELECT department FROM employees GROUP BY department;

-- 多列去重
SELECT department, job_title FROM employees GROUP BY department, job_title;

-- 创建测试表
CREATE TABLE test_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    col1 VARCHAR(50),
    col2 VARCHAR(50),
    col3 INT
);

-- 填充测试数据（使用随机数据）

-- 测试DISTINCT性能
EXPLAIN ANALYZE SELECT DISTINCT col1, col2 FROM test_table;

-- 测试GROUP BY性能  
EXPLAIN ANALYZE SELECT col1, col2 FROM test_table GROUP BY col1, col2;

-- 为测试列创建索引
CREATE INDEX idx_test ON test_table(col1, col2);

-- 使用覆盖索引时，GROUP BY有时更高效
SELECT col1, col2 FROM test_table GROUP BY col1, col2; -- 可能使用索引
SELECT DISTINCT col1, col2 FROM test_table; -- 同样可能使用索引

-- 获取所有唯一的部门名称
SELECT DISTINCT department_name FROM departments;

-- 计算不同部门的数量
SELECT COUNT(DISTINCT department_id) FROM employees;

-- 在子查询中使用DISTINCT
SELECT * FROM products 
WHERE category_id IN (SELECT DISTINCT category_id FROM active_categories);

-- 计算每个部门的平均工资
SELECT department, AVG(salary) 
FROM employees 
GROUP BY department;

-- 找出员工数量超过10人的部门
SELECT department, COUNT(*) as emp_count
FROM employees 
GROUP BY department 
HAVING emp_count > 10;

-- 按部门和职位统计员工数量
SELECT department, job_title, COUNT(*)
FROM employees 
GROUP BY department, job_title;

-- 两者都会将多个NULL值视为一个
SELECT DISTINCT nullable_column FROM table;
SELECT nullable_column FROM table GROUP BY nullable_column;

-- DISTINCT + ORDER BY
SELECT DISTINCT department 
FROM employees 
ORDER BY department;

-- GROUP BY + ORDER BY  
SELECT department 
FROM employees 
GROUP BY department 
ORDER BY department;

-- 获取前5个不同的部门
SELECT DISTINCT department FROM employees LIMIT 5;

-- 使用GROUP BY实现相同效果
SELECT department FROM employees GROUP BY department LIMIT 5;