Dùng subquery lấy giá trị lớn/nhỏ nhất khi join 2 bảng với nhau

Giả sử bạn có 2 bảng trong cơ sở dữ liệu. Một bảng lưu họ tên nhân viên. Bảng còn lại lưu thông tin lương (history của lương). Tương ứng với một nhân viên thì có nhiều thông tin lương. Bạn muốn truy vấn (query) trả về tương ứng với một nhân bạn sẽ chỉ lấy duy nhất một và chỉ một thông tin lương có ngày áp dụng lớn nhất hoặc nhỏ nhất từ bảng lương mà thôi.

Để hiểu sâu hơn chúng ta đi vào ví dụ chi tiết bên dưới:

1. Tạo 2 bảng lưu thông tin (GKN_Employee, GKN_SalaryHistory)
2. Thêm dữ liệu vào 2 bảng vừa tạo
3. Xem dữ liệu của 2 bảng
4. Thực thiện truy vấn (query) từ 2 bảng dữ liệu vừa tạo

/*  Tạo bảng lưu thông Nhân viên */
Create table GKN_Employee (ID int primary key,FullName nvarchar (200))
/* Tạo bảng lưu thông tin lương */

Create table GKN_SalaryHistory (
[Index] int primary key,
EmployeeID int foreign key REFERENCES GKN_Employee(ID),
Salary decimal,
AppliedDate datetime)
/* Thêm thông tin nhân viên */

INSERT INTO GKN_Employee VALUES(1, N'Nguyễn Khánh Hưng')
INSERT INTO GKN_Employee VALUES(2, N'Đoàn Thanh Thúy')
INSERT INTO GKN_Employee VALUES(3, N'Trần Xuân Vũ')
/* Thêm thông tin lương */
/* Nguyễn Khánh Hưng */

INSERT INTO GKN_SalaryHistory VALUES(1, 1, 500000, '1/1/2009')
INSERT INTO GKN_SalaryHistory VALUES(2, 1, 700000, '5/2/2009')
INSERT INTO GKN_SalaryHistory VALUES(3, 1, 1200000, '5/2/2010')
/* Đoàn Thanh Thúy */

INSERT INTO GKN_SalaryHistory VALUES(4, 2, 200000, '3/6/2009')
INSERT INTO GKN_SalaryHistory VALUES(5, 2, 900000, '5/19/2009')
INSERT INTO GKN_SalaryHistory VALUES(6, 2, 1000000, '12/22/2009')
/* Trần Xuân Vũ */

INSERT INTO GKN_SalaryHistory VALUES(7, 3, 4500000, '4/6/2009')
INSERT INTO GKN_SalaryHistory VALUES(8, 3, 7000000, '5/2/2010')
INSERT INTO GKN_SalaryHistory VALUES(9, 3, 12000000, '10/2/2010')
/* Xem dữ liệu */

select * from GKN_Employee
select * from GKN_SalaryHistory

/* Tương ứng với mỗi nhân viên: */

/* 1. Lấy thông tin lương có ngày áp dụng lớn nhất */

SELECT E.*, S.*

FROM GKN_Employee AS E  

INNER JOIN GKN_SalaryHistory AS S ON E.ID = S.EmployeeID

WHERE

 S.AppliedDate = (SELECT TOP 1 AppliedDate

FROM GKN_SalaryHistory

WHERE EmployeeID = S.EmployeeID

ORDER BY AppliedDate DESC)

/* 2. Lấy thông tin lương có ngày áp dụng nhỏ nhất */

SELECT E.*, S.*

FROM GKN_Employee AS E

INNER JOIN GKN_SalaryHistory AS S ON E.ID = S.EmployeeID

WHERE

 S.AppliedDate = (SELECT TOP 1 AppliedDate

FROM GKN_SalaryHistory

WHERE EmployeeID = S.EmployeeID

ORDER BY AppliedDate ASC)

=> kết quả thu được: phần trên trả về những record có giá trị ngày áp dụng lớn nhất của mỗi nhân viên, phần dưới giá trị nhỏ nhất.

Giải thích: 2 câu truy vấn (query) ở trên chỉ khác nhau về câu lệnh ORDER BY, nếu lấy theo giá trị lớn nhất thì dùng thuộc tính DESC, để sắp xếp ngày áp dụng giảm dần; ngược lại lấy theo giá trị nhỏ nhất thì dùng thuộc tính ASC, để sắp xếp ngày áp dụng tăng dần.

-> have a nice night