تعبيرات CASE في SELECT

CASE: التعبير الشرطي في SQL

SELECT order_id,
       amount,
       CASE WHEN amount > 100 THEN 'large' ELSE 'small' END AS size
FROM orders;

صيغة CASE البحثية

SELECT score,
       CASE
         WHEN score >= 90 THEN 'A'
         WHEN score >= 80 THEN 'B'
         WHEN score >= 70 THEN 'C'
         ELSE 'F'
       END AS grade
FROM exam_results;

صيغة CASE البسيطة

SELECT status_code,
       CASE status_code
         WHEN 1 THEN 'active'
         WHEN 2 THEN 'paused'
         WHEN 3 THEN 'closed'
         ELSE 'unknown'
       END AS status_label
FROM accounts;

CASE البحثية مقابل البسيطة: متى تستخدم كلًا منهما

-- Simple CASE will NOT catch NULL here
SELECT CASE WHEN bonus IS NULL THEN 'no bonus'
            ELSE 'has bonus'
       END AS bonus_flag
FROM employees;

ترتيب فروع WHEN مهم

-- Correct order: highest threshold first
SELECT CASE
         WHEN score >= 90 THEN 'A'
         WHEN score >= 80 THEN 'B'
         ELSE 'C or below'
       END AS grade
FROM exam_results;

ELSE وNULL الضمنية

-- No ELSE: anything not 1/2 becomes NULL
SELECT CASE flag WHEN 1 THEN 'yes' WHEN 2 THEN 'no' END AS label
FROM toggles;

التجميع الشرطي (نمط Pivot)

SELECT
  SUM(CASE WHEN status = 'paid'    THEN 1 ELSE 0 END) AS paid_orders,
  SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending_orders,
  SUM(CASE WHEN status = 'paid'    THEN amount ELSE 0 END) AS paid_revenue
FROM orders;

تتجاهل COUNT قيم NULL: اختصار باستخدام CASE

SELECT
  COUNT(CASE WHEN status = 'paid' THEN 1 END)    AS paid_orders,
  COUNT(CASE WHEN status = 'pending' THEN 1 END) AS pending_orders
FROM orders;

استخدام CASE في ORDER BY للفرز المخصص

SELECT order_id, status
FROM orders
ORDER BY
  CASE status
    WHEN 'urgent' THEN 1
    WHEN 'normal' THEN 2
    ELSE 3
  END,
  order_id;

اتساق الأنواع بين الفروع

-- Keep branches the same type
SELECT CASE WHEN qty > 0 THEN CAST(qty AS VARCHAR)
            ELSE 'none'
       END AS qty_label
FROM inventory;

استخدام CASE في WHERE للتصفية الشرطية

SELECT order_id, amount, region
FROM orders
WHERE amount > CASE region
                 WHEN 'US'  THEN 100
                 WHEN 'EU'  THEN 90
                 ELSE 50
               END;

اختبار سريع

مراجعة