Sử dụng CTE trong MySQL

Cú pháp WITH t1 AS (...): Định nghĩa một tập kết quả tạm thời t1, gọi là Common Table Expression (CTE). CTE cho phép tái sử dụng phần truy vấn phức tạp này nhiều lần trong câu truy vấn chính mà không cần viết lại.

WITH t1 AS (
    SELECT s.staff_id, post, TIMESTAMPDIFF(SECOND, first_clockin, last_clockin) / 3600 AS total_hours
    FROM staff_tb s 
    JOIN attendent_tb a ON s.staff_id = a.staff_id
)
SELECT post, ROUND(AVG(total_hours), 3) AS work_hours
FROM t1
GROUP BY post
ORDER BY work_hours DESC;

JOIN: Kết hợp dữ liệu từ hai bảng staff_tb và attendent_tb dựa trên trường staff_id. Điều này tạo ra một tập hợp các bản ghi có cùng staff_id từ cả hai bảng.

TIMESTAMPDIFF: Hàm này tính toán sự khác biệt giữa hai thời điểm first_clockin và last_clockin theo đơn vị giây.

Tóm tắt: Truy vấn này tính trung bình thời gian làm việc (tính bằng giờ) cho mỗi chức vụ. Kết quả được sắp xếp theo thứ tự giảm dần của thời gian làm việc.

Ví dụ khác:

SELECT vip, IF(SUM(order_price) > 0, SUM(order_price), 0) AS order_total
FROM uservip_tp u
LEFT JOIN order_tb o ON o.user_id = u.user_id
GROUP BY vip
ORDER BY order_total DESC;

SELECT 
    s.staff_id,
    s.staff_name, 
    CONCAT(ROUND(sa.dock_salary / sa.normal_salary * 100, 1), '%') AS dock_ratio
FROM staff_tb s
JOIN salary_tb sa ON sa.staff_id = s.staff_id
WHERE department='dep1'
ORDER BY dock_ratio DESC;

Hai truy vấn sau đây tính toán thời gian trung bình giữa hai thời điểm:

SELECT ROUND(
        AVG(
            (UNIX_TIMESTAMP(eat.out_time) - UNIX_TIMESTAMP(et.create_time)) / 3600
        ), 3
    ) AS time
FROM express_tb et
JOIN exp_action_tb eat ON et.exp_number = eat.exp_number;

SELECT ROUND(
        AVG(
            TIMESTAMPDIFF(MINUTE, e.create_time, a.out_time) / 60
        ), 3
    ) AS time
FROM express_tb e
JOIN exp_action_tb a ON e.exp_number = a.exp_number;

Lưu ý về độ chính xác: Hai truy vấn trên có thể gặp vấn đề về độ chính xác do cách tính toán thời gian.

Trong truy vấn đầu tiên, dù sử dụng UNIX_TIMESTAMP để tính số giây, kết quả vẫn bị ảnh hưởng bởi các phép tính số thực.

Trong truy vấn thứ hai, việc sử dụng TIMESTAMPDIFF với đơn vị phút khiến mất mát thông tin về phần giây, dẫn đến sai số nhỏ.

Thẻ: mysql cte TIMESTAMPDIFF JOIN

Đăng vào ngày 1 tháng 6 lúc 05:19

Thành phố Cuồng loạn

Sử dụng CTE trong MySQL

Thẻ Phổ Biến