Here are some basic examples of using CTEs in SQL:

Calculating the cumulative sum of a column:

WITH cumulative_sum AS (
  SELECT 
    order_id, 
    order_date, 
    order_total, 
    SUM(order_total) OVER (ORDER BY order_date) AS running_total
  FROM orders
)
SELECT 
  order_id, 
  order_date, 
  order_total, 
  running_total
FROM cumulative_sum;

Breaking down a complex query into multiple steps:

WITH customer_info AS (
  SELECT 
    customer_id, 
    customer_name, 
    city, 
    state
  FROM customers
),
order_info AS (
  SELECT 
    order_id, 
    customer_id, 
    order_date, 
    order_total
  FROM orders
)
SELECT 
  customer_info.customer_name, 
  order_info.order_date, 
  order_info.order_total
FROM customer_info
JOIN order_info 
  ON customer_info.customer_id = order_info.customer_id;

Reusing intermediate results:

WITH top_customers AS (
  SELECT 
    customer_id, 
    SUM(order_total) AS total_spend
  FROM orders
  GROUP BY customer_id
),
top_10_customers AS (
  SELECT 
    customer_id
  FROM top_customers
  ORDER BY total_spend DESC
  LIMIT 10
)
SELECT 
  customer_id, 
  order_date, 
  order_total
FROM orders
WHERE customer_id IN (SELECT customer_id FROM top_10_customers);

In these examples, the CTEs are used to simplify the overall structure of the query and make it easier to understand and maintain.
ต่อไปนี้เป็นตัวอย่างพื้นฐานของการใช้ CTE ใน SQL: การคำนวณผลรวมสะสมของคอลัมน์: WITH cumulative_sum AS ( SELECT order_id, order_date, order_total, SUM(order_total) OVER (ORDER BY order_date) AS running_total FROM orders ) SELECT order_id, order_date, order_total, running_total จาก cumulative_sum; แบ่งการสืบค้นที่ซับซ้อนออกเป็นหลายขั้นตอน: ด้วย customer_info AS ( SELECT customer_id, customer_name, เมือง, รัฐ FROM ลูกค้า ), order_info AS ( SELECT order_id, customer_id, order_date, order_total FROM orders ) SELECT customer_info.customer_name, order_info.order_date, order_info order_total จาก customer_info เข้าร่วม order_info บน customer_info.customer_id = order_info.customer_id; การนำผลลัพธ์ระดับกลางมาใช้ซ้ำ: WITH top_customers AS ( SELECT customer_id, SUM(order_total) AS Total_spend FROM orders GROUP BY customer_id ), top_10_customers AS ( SELECT customer_id FROM top_customers ORDER BY Total_spend DESC LIMIT 10 ) SELECT customer_id, order_date, order_total FROM orders WHERE customer_id IN ( เลือก customer_id จาก top_10_customers); ในตัวอย่างเหล่านี้ CTE จะใช้เพื่อลดความซับซ้อนของโครงสร้างโดยรวมของการสืบค้น และทำให้ง่ายต่อการเข้าใจและบำรุงรักษา
spam fails_task lang_mismatch pii not_appropriate hate_speech sexual_content quality toxicity humor helpfulness creativity violence