1

SELECT DISTINCT a, b FROM table;  -- 对(a,b)组合整体去重

1

2

-- 错误理解：以为只对name去重

SELECT DISTINCT name, class FROM students; 

1

2

3

INSERT INTO students (name, class, score) VALUES (NULL, '三班', 90);

SELECT DISTINCT name FROM students;

1

2

-- 当表有百万行时慎用

SELECT DISTINCT text_column FROM huge_table;

1

2

3

4

5

6

7

-- 先通过WHERE缩小范围再去重

SELECT DISTINCT text_column

FROM huge_table

WHERE create_time > '2023-01-01';

-- 或添加索引（对text类型有限制）

ALTER TABLE huge_table ADD INDEX idx_text(text_column(20)); -- 前缀索引

1

2

3

SELECT DISTINCT class

FROM students

ORDER BY score DESC; -- 错误！score不在SELECT中

1

2

3

4

5

6

7

8

9

10

-- 方案1：排序字段必须在SELECT中

SELECT DISTINCT class, MAX(score) AS max_score

FROM students

GROUP BY class

ORDER BY max_score DESC;

-- 方案2：子查询

SELECT DISTINCT class FROM (

  SELECT class, score FROM students ORDER BY score DESC

) AS tmp;

1

2

3

4

5

-- 统计不重复的班级数量

SELECT COUNT(DISTINCT class) FROM students;

-- 错误用法（语法无效）：

SELECT DISTINCT COUNT(class) FROM students;

1

2

-- 尝试计算不同班级的平均分（错误！）

SELECT DISTINCT class, AVG(score) FROM students;

1

2

3

SELECT class, AVG(score)

FROM students

GROUP BY class;  -- 这才是标准解法

1

SELECT DISTINCT class FROM students LIMIT 2;

1

SHOW VARIABLES LIKE 'tmp_table_size'; -- 默认16MB

1

2

3

4

5

6

7

CREATE TABLE orders (

    id INT PRIMARY KEY,

    product_id INT,

    user_id INT,

    amount DECIMAL(10,2),

    coupon_code VARCHAR(20) -- 允许为NULL

);

1

2

3

4

5

6

7

8

-- 方案1：基础写法

SELECT DISTINCT user_id, coupon_code

FROM orders

WHERE coupon_code IS NOT NULL; -- 若需包含NULL则去掉WHERE

???????-- 方案2：大数据量优化（添加联合索引）

ALTER TABLE orders ADD INDEX idx_user_coupon(user_id, coupon_code);

SELECT DISTINCT user_id, coupon_code FROM orders;