SELECT DISTINCT a, b FROM table;  -- 對(duì)(a,b)組合整體去重

-- 錯(cuò)誤理解：以為只對(duì)name去重
SELECT DISTINCT name, class FROM students;  

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

???????SELECT DISTINCT name FROM students;

-- 當(dāng)表有百萬(wàn)行時(shí)慎用
SELECT DISTINCT text_column FROM huge_table; 

-- 先通過(guò)WHERE縮小范圍再去重
SELECT DISTINCT text_column 
FROM huge_table 
WHERE create_time > '2023-01-01';

-- 或添加索引（對(duì)text類型有限制）
ALTER TABLE huge_table ADD INDEX idx_text(text_column(20)); -- 前綴索引

SELECT DISTINCT class 
FROM students 
ORDER BY score DESC; -- 錯(cuò)誤！score不在SELECT中

-- 方案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;

-- 統(tǒng)計(jì)不重復(fù)的班級(jí)數(shù)量
SELECT COUNT(DISTINCT class) FROM students; 

-- 錯(cuò)誤用法（語(yǔ)法無(wú)效）：
SELECT DISTINCT COUNT(class) FROM students; 

-- 嘗試計(jì)算不同班級(jí)的平均分（錯(cuò)誤?。?
SELECT DISTINCT class, AVG(score) FROM students; 

SELECT class, AVG(score) 
FROM students 
GROUP BY class;  -- 這才是標(biāo)準(zhǔn)解法

SELECT DISTINCT class FROM students LIMIT 2;

SHOW VARIABLES LIKE 'tmp_table_size'; -- 默認(rèn)16MB

CREATE TABLE orders (
    id INT PRIMARY KEY,
    product_id INT,
    user_id INT,
    amount DECIMAL(10,2),
    coupon_code VARCHAR(20) -- 允許為NULL
);

-- 方案1：基礎(chǔ)寫(xiě)法
SELECT DISTINCT user_id, coupon_code 
FROM orders 
WHERE coupon_code IS NOT NULL; -- 若需包含NULL則去掉WHERE

???????-- 方案2：大數(shù)據(jù)量?jī)?yōu)化（添加聯(lián)合索引）
ALTER TABLE orders ADD INDEX idx_user_coupon(user_id, coupon_code);
SELECT DISTINCT user_id, coupon_code FROM orders;