SQL join - Select the last records in a one-to-many relationship

Chào các bạn, ở bài viết hôm nay mình sẽ cùng các bạn đi giải quyết một bài toán khá đơn giản bằng các cách khác nhau sử dụng kiến thức truy vấn cơ sở dữ liệu với hệ quản trị cơ sở dữ liệu MySQL.

Đặt vấn đề

Mình có table customers chứa thông tin khách hàng bao gồm 3 trường custID, custFirst và custLast, table sales chứa thông tin về lịch sử bán hàng gồm các trường saleID, dateSale, saleAmount và custID. Quan hệ giữa customers và sales là 1 - n.
Bài toán đặt ra là giờ mình muốn lấy danh sách các customers và lịch sử bán hàng gần nhất cho customer đó.
Các bạn có thể chạy lệnh sql bên dưới để tạo cấu trúc các table và sample data.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
CREATE TABLE `customers` (
  `custID` int(10) NOT NULL auto_increment,
  `custFirst` varchar(50) default NULL,
  `custLast` varchar(50) default NULL,
  PRIMARY KEY  (`custID`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

insert  into `customers`(`custID`,`custFirst`,`custLast`) values (1,'John','Smith'),(2,'Sally','Fields'),(3,'Winston','Churchill');

CREATE TABLE `sales` (
  `saleID` int(10) NOT NULL auto_increment,
  `saleDate` datetime default NULL,
  `saleAmount` double default NULL,
  `custID` int(10) default '0',
  PRIMARY KEY  (`saleID`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

insert  into `sales`(`saleID`,`saleDate`,`saleAmount`,`custID`) values (1,'2008-10-20 09:12:00',20,1),(2,'2007-12-03 18:45:00',14,1),(3,'2008-02-13 16:00:00',10,3),(4,'2007-03-14 16:00:00',30,3),(5,'2007-05-14 14:48:00',18,3),(6,'2008-10-22 19:00:00',57,1);

Sample data table customers.

1
2
3
4
5
6
7
8
+--------+-----------+-----------+
| custID | custFirst | custLast  |
+--------+-----------+-----------+
|      1 | John      | Smith     |
|      2 | Sally     | Fields    |
|      3 | Winston   | Churchill |
+--------+-----------+-----------+
3 rows in set (0,00 sec)

Sample data table sales.

1
2
3
4
5
6
7
8
9
10
11
+--------+---------------------+------------+--------+
| saleID | saleDate            | saleAmount | custID |
+--------+---------------------+------------+--------+
|      1 | 2008-10-20 09:12:00 |         20 |      1 |
|      2 | 2007-12-03 18:45:00 |         14 |      1 |
|      3 | 2008-02-13 16:00:00 |         10 |      3 |
|      4 | 2007-03-14 16:00:00 |         30 |      3 |
|      5 | 2007-05-14 14:48:00 |         18 |      3 |
|      6 | 2008-10-22 19:00:00 |         57 |      1 |
+--------+---------------------+------------+--------+
6 rows in set (0,00 sec)

Giải pháp

Đầu tiên mình sẽ tạo ra bảng tạm, mục đích của bảng này là lấy ra những record sales gần nhất ứng với mỗi custID trong bảng sales.
Mình sử dụng câu lệnh sql sau:

1
2
3
4
5
SELECT s1.*
		FROM sales as s1
		LEFT JOIN sales AS s2
		     ON s1.custID = s2.custID AND s1.saledate < s2.saledate
		WHERE s2.custID IS NULL

Kết quả sẽ như sau:

1
2
3
4
5
6
7
+--------+---------------------+------------+--------+
| saleID | saleDate            | saleAmount | custID |
+--------+---------------------+------------+--------+
|      3 | 2008-02-13 16:00:00 |         10 |      3 |
|      6 | 2008-10-22 19:00:00 |         57 |      1 |
+--------+---------------------+------------+--------+
2 rows in set (0,00 sec)

Bây giờ mình sẽ join với bảng customers thông qua custID là xong.

1
2
3
4
5
6
7
8
9
10
11
12
13

SELECT
    customers.*, sale_tmp.*
FROM
    customers

    LEFT JOIN ( SELECT s1.*
		FROM sales as s1
		LEFT JOIN sales AS s2
		     ON s1.custID = s2.custID AND s1.saledate < s2.saledate
		WHERE s2.custID IS NULL ) as sale_tmp

    ON (customers.custID = sale_tmp.custID)

Kết quả:

1
2
3
4
5
6
7
8
+--------+-----------+-----------+--------+---------------------+------------+--------+
| custID | custFirst | custLast  | saleID | saleDate            | saleAmount | custID |
+--------+-----------+-----------+--------+---------------------+------------+--------+
|      3 | Winston   | Churchill |      3 | 2008-02-13 16:00:00 |         10 |      3 |
|      1 | John      | Smith     |      6 | 2008-10-22 19:00:00 |         57 |      1 |
|      2 | Sally     | Fields    |   NULL | NULL                |       NULL |   NULL |
+--------+-----------+-----------+--------+---------------------+------------+--------+
3 rows in set (0,00 sec)

Như kết quả trả về trên thì hai khách hàng John và Winston có lịch sử mua hàng gần nhất còn khách hàng Sally thì chưa mua lần nào.

Có một cách khác để giải quyết bài toán này. Mình viết lại câu truy vấn như sau:

1
2
3
4
5
6
SELECT c.*, s1.*
FROM customers c
LEFT JOIN sales s1 ON (c.custID = s1.custID)
LEFT OUTER JOIN sales s2 ON (c.custID = s2.custID AND
    (s1.saleDate < s2.saleDate OR s1.saleDate = s2.saleDate AND s1.saleID < s2.saleID))
WHERE s2.saleID IS NULL;

Kết quả cũng tương tự như cách trên.
Tuy nhiên, sẽ có lúc gặp trường hợp một customer có nhiều hơn một bản ghi sales gần nhất. Để giải quyết vấn đề này các bạn có thể dùng limit.

Kết luận

Hi vọng bài viết sẽ hữu ích cho các bạn, cảm ơn đã đọc (bow).