การเพิ่มคอลัมน์นับจำนวน Last Name ซ้ำด้วย JOIN กับ Subquery GROUP BY
เมื่อต้องการแสดงข้อมูลแต่ละแถว พร้อมกับจำนวนครั้งที่ Last Name นั้นปรากฏในตารางทั้งหมด เราสามารถ JOIN ตารางกับ Subquery ที่ GROUP BY LastName และ COUNT(*)
สร้างตารางและเพิ่มข้อมูล
mysql> CREATE TABLE DemoTable (
Id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
FirstName VARCHAR(100),
LastName VARCHAR(100)
);
Query OK, 0 rows affected (1.00 sec)
mysql> INSERT INTO DemoTable(FirstName, LastName) VALUES('David','Miller');
mysql> INSERT INTO DemoTable(FirstName, LastName) VALUES('Carol','Miller');
mysql> INSERT INTO DemoTable(FirstName, LastName) VALUES('John','Doe');แสดงข้อมูลทั้งหมด
mysql> SELECT * FROM DemoTable;ผลลัพธ์:
+----+-----------+----------+
| Id | FirstName | LastName |
+----+-----------+----------+
| 1 | David | Miller |
| 2 | Carol | Miller |
| 3 | John | Doe |
+----+-----------+----------+
3 rows in set (0.00 sec)คำสั่ง JOIN กับ Subquery นับ LastName
mysql> SELECT Id, FirstName, LastName, Count
FROM DemoTable tbl1
JOIN (
SELECT COUNT(*) AS Count, LastName FROM DemoTable GROUP BY LastName
) tbl2 USING (LastName);ผลลัพธ์:
+----+-----------+----------+-------+
| Id | FirstName | LastName | Count |
+----+-----------+----------+-------+
| 1 | David | Miller | 2 |
| 2 | Carol | Miller | 2 |
| 3 | John | Doe | 1 |
+----+-----------+----------+-------+
3 rows in set (0.00 sec)คำอธิบาย
- Subquery
tbl2: คำนวณ Count ต่อ LastName (Miller=2, Doe=1) JOIN ... USING (LastName): รวมกลับกับตารางต้นทาง (tbl1) ตาม LastName- ผลคือทุกแถวได้คอลัมน์ Count เพิ่มมาแสดงจำนวน Last Name นั้นในตารางทั้งหมด
- วิธีอื่นใน MySQL 8.0+: ใช้ Window Function
COUNT(*) OVER (PARTITION BY LastName)
