Examples of case Expression

Result Set Transformations

• 行列转置

• example

  • 通过GROUP BY得到TABLE

    SELECT monthname(rental_date) rental_month, COUNT(*) num_rentals
    FROM rental
    WHERE rental_date BETWEEN '2005-05-01' AND '2005-08-01'
    GROUP BY monthname(rental_date);
    

  • 通过CASE WHEN THEN END GROUP BY

    SELECT
    SUM(CASE WHEN monthname(rental_date) = 'May' THEN 1
      ELSE 0 END) May_rentals,
    SUM(CASE WHEN monthname(rental_date) = 'June' THEN 1
      ELSE 0 END) June_rentals,
    SUM(CASE WHEN monthname(rental_date) = 'July' THEN 1
      ELSE 0 END) Junly_rentals
    FROM rental
    WHERE rental_date BETWEEN '2005-05-01' AND '2005-08-01';
    

  • 大概逻辑

    1. 通过CASE判断是否符合条件，若符合条件，返回1，否则返回0
    2. 然后用SUM加和计算得到个月份的频数

Checking for Existence

• demo

  CASE WHEN EXISTS() THEN END
  

Division-by-Zero Errors

• example

  SELECT c.first_name,c.last_name, sum(p.amount) tot_payment_amt,
       count(p.amount) num_payments,
       sum(p.amount) /
  CASE WHEN count(p.amount) = 0 THEN 1
  ELSE count(p.amount)
  END avg_payment
  FROM customer c
  LEFT OUTER JOIN payment p
    on c.customer_id = p.customer_id
  GROUP BY c.first_name, c.last_name;
  

  判断分母是否为0，分母为0时，返回1

Conditional Updates

• example

  UPDATE customer
  SET active =
  CASE 90 <= (SELECT datediff(now(), max(rental_date))
      FROM rental r
      WHERE r.customer_id = customer.customer_id)
  THEN 0
  ELSE 1
  END
  WHERE active = 1;
  

  • 判断用户的活跃程度，将最近90天内未活跃的用户设定为0，否则设定为1

Handeling Null Values

• demo

  WHEN $fld IS NULL THEN $true_return ELSE $else_return END;