WITH: nomlangan so‘rovlar

Murakkab so‘rovlar tezda chalkashib ketadi: ichki so‘rov ichida ichki so‘rov… WITH (CTE — Common Table Expression) ularni nomlangan, tartibli qadamlarga ajratadi.

Qolip

WITH nom AS (
  SELECT …
)
SELECT … FROM nom;

nom — shu so‘rov davomida mavjud bo‘ladigan vaqtinchalik jadval.

Misol: o‘rtacha balli talabalar

Avval har bir talabaning o‘rtacha balini hisoblaymiz, keyin undan foydalanamiz:

WITH avg_scores AS (
  SELECT student_id, AVG(score) AS avg_score
  FROM grades
  GROUP BY student_id
)
SELECT s.first_name, s.last_name, ROUND(a.avg_score, 1) AS avg_score
FROM avg_scores a
JOIN students s ON s.id = a.student_id
WHERE a.avg_score >= 85
ORDER BY a.avg_score DESC;

Xuddi shuni ichki so‘rov bilan ham yozsa bo‘ladi, lekin WITH bilan o‘qish ancha oson: har bir qadamning nomi bor.

Bir nechta CTE

Vergul bilan ketma-ket yoziladi, keyingisi oldingisidan foydalana oladi:

WITH order_totals AS (
  SELECT order_id, SUM(quantity * unit_price) AS total
  FROM order_items
  GROUP BY order_id
),
big_orders AS (
  SELECT order_id FROM order_totals WHERE total > 10000000
)
SELECT o.payment_method, COUNT(*) AS big_orders
FROM big_orders b
JOIN orders o ON o.id = b.order_id
GROUP BY o.payment_method;

Rekursiv CTE

WITH RECURSIVE o‘zini o‘zi chaqiradi — daraxtsimon ma’lumot uchun (rahbar → bo‘ysunuvchi → uning bo‘ysunuvchisi…):

WITH RECURSIVE chain AS (
  SELECT id, first_name, manager_id, 1 AS level FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.first_name, e.manager_id, c.level + 1
  FROM employees e JOIN chain c ON e.manager_id = c.id
)
SELECT level, COUNT(*) AS people FROM chain GROUP BY level ORDER BY level;

Birinchi qism — boshlang‘ich nuqta (bosh direktor), ikkinchisi — har safar bir pog‘ona pastga tushadi.

Esda qoldiring

• WITH nom AS (…) — so‘rovni nomlangan qadamlarga bo‘ladi.
• Bir nechta CTE vergul bilan; keyingisi oldingisini ishlata oladi.
• WITH RECURSIVE — daraxt va zanjirlar uchun.