Hướng dẫn rownum trong mysql - rownum trong mysql

Trong bài viết này sẽ hướng dẫn các bạn cách mô phỏng hàm row_number() trong MySQL để thêm giá trị số duy nhất cho mỗi row hoặc mỗi group của tập kết quả trả về.

Chú là hàm ROW_NUMBER() chỉ chạy trên MySQL 8.0

Giới thiệu hàm row_number

row_number() là một hàm thứ bậc, nó trả về các số liên tiếp của hàng, bắt đầu từ 1 cho hàng đầu tiên. Thông thường sử dụng hàm row_number() để xuất ra các báo cáo rõ ràng.

Đối với MySQL version thấp hơn 8.0 sẽ không được hỗ trợ hàm row_number() như Microsoft SQL Server, Oracle, hoặc PostgreSQL. May mắn là MySQL cung cấp các biết session và do đó chúng ta có thể mô phỏng lại hàm row_number()

Thêm row number cho mỗi dòng

Để có thể sử dụng row_number() thì chúng ta phải sử dụng các biến session trong truy vấn.

Câu lệnh dưới đây sẽ lấy ra 5 nhân viên từ bảng

SELECT 
    (@row_number:[email protected]row_number + 1) AS num, firstName, lastName
FROM
    employees,(SELECT @row_number:=0) AS t
LIMIT 5;

SET @row_number = 0;
 
SELECT 
    (@row_number:[email protected]row_number + 1) AS num, firstName, lastName
FROM
    employees
LIMIT 5;

Các câu lệnh ở trên:

• Trong câu lệnh đầu tiên, chúng ta định nghĩa 1 biến

  SELECT 
      (@row_number:[email protected]row_number + 1) AS num, firstName, lastName
  FROM
      employees,(SELECT @row_number:=0) AS t
  LIMIT 5;
  

  3 và gán giá trị là 0.

  SELECT 
      (@row_number:[email protected]row_number + 1) AS num, firstName, lastName
  FROM
      employees,(SELECT @row_number:=0) AS t
  LIMIT 5;
  

  3 là một biến session được chỉ ra bới tiền tố

  SELECT 
      (@row_number:[email protected]row_number + 1) AS num, firstName, lastName
  FROM
      employees,(SELECT @row_number:=0) AS t
  LIMIT 5;
  

  5.
• Trong câu lệnh thứ 2, chúng ta select dữ liệu từ bảng

  SELECT 
      (@row_number:[email protected]row_number + 1) AS num, firstName, lastName
  FROM
      employees,(SELECT @row_number:=0) AS t
  LIMIT 5;
  

  2 và tăng giá trị

  SELECT 
      (@row_number:[email protected]row_number + 1) AS num, firstName, lastName
  FROM
      employees,(SELECT @row_number:=0) AS t
  LIMIT 5;
  

  3 lên 1 cho mỗi row.

  SELECT 
      (@row_number:[email protected]row_number + 1) AS num, firstName, lastName
  FROM
      employees,(SELECT @row_number:=0) AS t
  LIMIT 5;
  

  8 sẽ giới hạn kết quả trả về là 5.

Có 1 kỹ thuật khác là sử dụng session variable như là 1 derived table và cross join nó với bảng chính. Hãy xem truy vấn sau:

SELECT 
    (@row_number:[email protected]row_number + 1) AS num, firstName, lastName
FROM
    employees,(SELECT @row_number:=0) AS t
LIMIT 5;

Chú ý là derived table bắt buộc có alias cho chính nó để đảm bảo truy vấn đúng cú pháp

Thêm row number cho mỗi group

Chức năng

SELECT 
    (@row_number:[email protected]row_number + 1) AS num, firstName, lastName
FROM
    employees,(SELECT @row_number:=0) AS t
LIMIT 5;

Hãy xem bảng

SELECT
    customerNumber, paymentDate, amount
FROM
    payments
ORDER BY customerNumber;

SELECT
    customerNumber, paymentDate, amount
FROM
    payments
ORDER BY customerNumber;

Giả sử đối với mỗi customer, bạn muốn thêm một row number, và mỗi row number sẽ đặt lại bất cứ lúc nào customer number thay đổi.

Để làm điều này thì bạn phải sử dụng biến session, một cho row number và biết khác cho việc lưu customer number cũ để so sánh nó với số hiện tại, giống như trong truy vấn sau:

SELECT 
    @row_number:=CASE
        WHEN @customer_no = customerNumber THEN @row_number + 1
        ELSE 1
    END AS num,
    @customer_no:=customerNumber as CustomerNumber,
    paymentDate,
    amount
FROM
    payments
ORDER BY customerNumber;

Trong truy vấn trên, chúng ta sử dụng lệnh

SELECT
    customerNumber, paymentDate, amount
FROM
    payments
ORDER BY customerNumber;

SELECT 
    (@row_number:[email protected]row_number + 1) AS num, firstName, lastName
FROM
    employees,(SELECT @row_number:=0) AS t
LIMIT 5;

Bạn có thể sử dụng derived table và kỹ thuật cross join để có kết quả tương tự:

SELECT 
    @row_number:=CASE
        WHEN @customer_no = customerNumber THEN @row_number + 1
        ELSE 1
    END AS num,
    @customer_no:=customerNumber as CustomerNumber,
    paymentDate,
    amount
FROM
    payments,(SELECT @customer_no:=0,@row_number:=0) as t
ORDER BY customerNumber;