CASE WHEN  SQL. 18   
 


   CASE WHEN  SQL     .  18    : , ,  ,  ,  ,  ,  ,    .     SQLite,     .





 

CASE WHEN  SQL. 18   





  





 CASE     


      .      ,     ,     .

CASE WHEN     SQL:                .

   CASE     ,     :      .




  


     -,    , ,    .

     CASE:         ELSE.

    SQLite.  CASE ,     PostgreSQL, MySQL   SQL-.




  


1.      (#task-1)

2.    (#task-2)

3.      (#task-3)

4.       (#litres_trial_promo)

5.     (#litres_trial_promo)

6.    (#litres_trial_promo)

7.    - (#litres_trial_promo)

8.     (#litres_trial_promo)

9.    (#litres_trial_promo)

10.     (#litres_trial_promo)

11.     (#litres_trial_promo)

12.     (#litres_trial_promo)

13.   pivot   (#litres_trial_promo)

14.     (#litres_trial_promo)

15.    (#litres_trial_promo)

16.     (#litres_trial_promo)

17.     (#litres_trial_promo)

18.     (#litres_trial_promo)




 





 1.     



 

     ,   ,     .




CREATE TABLE orders(id INTEGER, total INTEGER);

INSERT INTO orders VALUES (1,150),(2,490),(3,900),(4,1200);




SELECT id, total,

CASE

WHEN total < 300 THEN 'small'

WHEN total < 800 THEN 'medium'

ELSE 'large'

END AS check_size

FROM orders

ORDER BY id;


 

[[1, 150, "small"], [2, 490, "medium"], [3, 900, "large"], [4, 1200, "large"]]


 

CASE    .

150  300,   small.

900         ELSE large.


  

 :   total < 800  total < 300,     medium.


 

   300.       ?

:    medium,    small    300.




 2.   



 

     ,        .




CREATE TABLE orders(id INTEGER, status TEXT);

INSERT INTO orders VALUES (1,'paid'),(2,'new'),(3,'cancelled'),(4,'refunded');




SELECT id, status,

CASE status

WHEN 'paid' THEN ''

WHEN 'new' THEN ''

WHEN 'cancelled' THEN ''

ELSE ''

END AS status_label

FROM orders

ORDER BY id;


 

[[1, "paid", ""], [2, "new", ""], [3, "cancelled", ""], [4, "refunded", ""]]


 

  CASE    status   .

    .

refunded   ,    ELSE.


  

  ELSE     :     ,     NULL.


 

    refunded.    ?

:    '',   4    ''.




 3.     



 

      ,     .




CREATE TABLE sales(id INTEGER, price INTEGER, qty INTEGER);

INSERT INTO sales VALUES (1,99,2),(2,199,3),(3,499,1),(4,1200,1);




SELECT

CASE

WHEN price < 150 THEN ' 150'

WHEN price < 500 THEN '150-499'

ELSE '500+'

END AS price_bucket,

SUM(price * qty) AS revenue

FROM sales

GROUP BY price_bucket

ORDER BY revenue DESC;




  .


   .

   ,     (https://www.litres.ru/book/raznoe/case-when-v-sql-18-zadach-dlia-otchiotov-74419462/)  .

      Visa, MasterCard, Maestro,    ,   ,     ,  PayPal, WebMoney, ., QIWI ,       .


