MySQL: GROUP BY ด้วย WHERE และ HAVING COUNT > 1

การใช้ GROUP BY ร่วมกับ WHERE (กรองก่อนจัดกลุ่ม) และ HAVING (กรองหลังจัดกลุ่ม) ช่วยให้สามารถหาข้อมูลที่ตรงเงื่อนไขและมีจำนวนซ้ำเกินกว่าที่กำหนดได้

รูปแบบคำสั่ง

SELECT columns
FROM table_name
WHERE condition_before_grouping
GROUP BY grouping_columns
HAVING COUNT(*) > threshold
ORDER BY sort_column DESC;

• WHERE - กรองแถว ก่อน จัดกลุ่ม
• GROUP BY - จัดกลุ่มแถวที่เหลือ
• HAVING - กรองกลุ่ม หลัง จัดกลุ่ม (ใช้ aggregate function ได้)
• ORDER BY - เรียงลำดับผลลัพธ์สุดท้าย

ตัวอย่างการใช้งาน

1. สร้างตารางและเพิ่มข้อมูล 19 แถว

CREATE TABLE GroupByWithWhereClause (
    Id INT NOT NULL AUTO_INCREMENT,
    IsDeleted TINYINT(1),
    MoneyStatus VARCHAR(20),
    UserId INT,
    PRIMARY KEY (Id)
);

INSERT INTO GroupByWithWhereClause (IsDeleted, MoneyStatus, UserId) VALUES
(0, 'Undone', 101), (0, 'done', 101), (0, 'done', 101),
(0, 'done', 102), (1, 'Undone', 102), (1, 'done', 102),
(0, 'Undone', 103), (0, 'done', 103), (0, 'done', 103), (0, 'done', 103),
(0, 'done', 104), (0, 'Undone', 104),
(1, 'Undone', 105), (1, 'done', 105), (1, 'done', 105), (0, 'done', 105),
(0, 'Undone', 106), (0, 'done', 106), (0, 'done', 106);

2. คำสั่ง: กรอง IsDeleted=0 และ MoneyStatus='done' แล้วจัดกลุ่ม UserId 3 หลักแรก ต้องมีมากกว่า 1 รายการ

SELECT * 
FROM GroupByWithWhereClause
WHERE IsDeleted = 0 AND MoneyStatus = 'done'
GROUP BY SUBSTR(UserId, 1, 3)
HAVING COUNT(*) > 1
ORDER BY Id DESC;

ผลลัพธ์:

+----+-----------+-------------+--------+
| Id | IsDeleted | MoneyStatus | UserId |
+----+-----------+-------------+--------+
| 18 |         0 | done        |    106 |
|  8 |         0 | done        |    103 |
|  2 |         0 | done        |    101 |
+----+-----------+-------------+--------+

คำอธิบายขั้นตอน

1. WHERE IsDeleted=0 AND MoneyStatus='done' - เหลือแถวที่ IsDeleted=0 และ MoneyStatus='done' (10 แถว: Id 2,3,4,7,8,9,10,11,16,18)
2. GROUP BY SUBSTR(UserId,1,3) - จัดกลุ่มโดย 3 หลักแรกของ UserId (ทั้งหมดคือ '101', '102', '103', '104', '105', '106' → กลุ่ม '101', '102', '103', '104', '105', '106')
3. HAVING COUNT(*) > 1 - เก็บเฉพาะกลุ่มที่มีมากกว่า 1 แถว
• กลุ่ม 101: Id 2,3 → 2 แถว ✓
• กลุ่ม 102: Id 4 → 1 แถว ✗
• กลุ่ม 103: Id 8,9,10 → 3 แถว ✓
• กลุ่ม 104: Id 11 → 1 แถว ✗
• กลุ่ม 105: Id 16 → 1 แถว ✗
• กลุ่ม 106: Id 17,18 → 2 แถว ✓
4. ORDER BY Id DESC - เรียง Id มากไปน้อย

ข้อสังเกตสำคัญ

• WHERE ไม่สามารถใช้ aggregate function (COUNT, SUM, AVG) ได้
• HAVING ใช้ aggregate function ได้
• SELECT * กับ GROUP BY อาจคืนค่าที่ไม่แน่นอน (MySQL default sql_mode) แนะนำระบุคอลัมน์ชัดเจนหรือใช้ ANY_VALUE()
• SUBSTR(UserId,1,3) จัดกลุ่มโดย prefix - หาก UserId เป็น INT จะแปลงเป็นสตริงอัตโนมัติ

แนวทางปฏิบัติที่ดีกว่า (MySQL 8.0+)

SELECT 
    SUBSTR(UserId, 1, 3) AS UserIdPrefix,
    COUNT(*) AS cnt,
    MAX(Id) AS MaxId
FROM GroupByWithWhereClause
WHERE IsDeleted = 0 AND MoneyStatus = 'done'
GROUP BY SUBSTR(UserId, 1, 3)
HAVING COUNT(*) > 1
ORDER BY MaxId DESC;

ระบุคอลัมน์ชัดเจน ใช้ aggregate function ใน SELECT ทำให้ผลลัพธ์คาดเดาได้และเป็นมาตรฐาน SQL