الاستعلامات الفرعية والتجزئة والمسارات الهرمية

WITH october_shipments AS (
 SELECT customer_id, charge FROM shipments
 WHERE booked_at >= DATE '2026-10-01' AND booked_at < DATE '2026-11-01'
), customer_totals AS (
 SELECT customer_id, SUM(charge) AS charge_total
 FROM october_shipments GROUP BY customer_id
)
SELECT customer_id, charge_total FROM customer_totals
WHERE charge_total >= 100
ORDER BY customer_id;

لكل مرحلة تفصيل واضح: الشحنة ثم العميل. تُعاد فقط العملاء ذوو إجمالي أكتوبر المؤهل.

WITH depots AS (
 SELECT 1 depot_id, CAST(NULL AS NUMBER) parent_id, 'Hub' label FROM dual UNION ALL
 SELECT 2, 1, 'East' FROM dual UNION ALL
 SELECT 3, 2, 'Gate' FROM dual
)
SELECT depot_id, LEVEL AS depth, SYS_CONNECT_BY_PATH(label, '/') AS route
FROM depots
START WITH parent_id IS NULL
CONNECT BY PRIOR depot_id = parent_id
ORDER SIBLINGS BY depot_id;

DEPOT_ID | DEPTH | ROUTE
1        | 1     | /Hub
2        | 2     | /Hub/East
3        | 3     | /Hub/East/Gate

تنتج السلسلة /Hub و/Hub/East و/Hub/East/Gate عند الأعماق 1 و2 و3.