ไวยากรณ์ CASE WHEN พร้อม OR:
SELECT yourColumnName1, ..., CASE WHEN yourColumnName2 = 0 OR yourColumnName2 IS NULL THEN 'yourMessage1' ELSE 'yourMessage2' END AS yourAliasName FROM yourTableName;
สร้างตาราง:
mysql> create table ReservationSystems ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, Name varchar(20), isSeatConfirmed tinyint ); Query OK, 0 rows affected (0.61 sec)
แทรกข้อมูล:
mysql> insert into ReservationSystems(Name,isSeatConfirmed) values('Larry',0); Query OK, 1 row affected (0.12 sec) mysql> insert into ReservationSystems(Name,isSeatConfirmed) values('Bob',1); Query OK, 1 row affected (0.15 sec) mysql> insert into ReservationSystems(Name,isSeatConfirmed) values('Mike',NULL); Query OK, 1 row affected (0.14 sec) mysql> insert into ReservationSystems(Name,isSeatConfirmed) values('David',1); Query OK, 1 row affected (0.17 sec) mysql> insert into ReservationSystems(Name,isSeatConfirmed) values('Carol',0); Query OK, 1 row affected (0.19 sec) mysql> insert into ReservationSystems(Name,isSeatConfirmed) values('James',1); Query OK, 1 row affected (0.11 sec)แสดงข้อมูล:
mysql> select * from ReservationSystems;
ผลลัพธ์:
+----+-------+-----------------+ | Id | Name | isSeatConfirmed | +----+-------+-----------------+ | 1 | Larry | 0 | | 2 | Bob | 1 | | 3 | Mike | NULL | | 4 | David | 1 | | 5 | Carol | 0 | | 6 | James | 1 | +----+-------+-----------------+ 6 rows in set (0.00 sec)
CASE WHEN กับ OR (0 หรือ NULL = ไม่ยืนยัน):
mysql> select Name, CASE WHEN isSeatConfirmed = 0 OR isSeatConfirmed IS NULL THEN 'YOUR SEAT IS NOT CONFIRMED' ELSE 'YOUR SEAT IS CONFIRMED' END AS SEATCONFIRMSTATUS FROM ReservationSystems;
ผลลัพธ์
+-------+----------------------------+ | Name | SEATCONFIRMSTATUS | +-------+----------------------------+ | Larry | YOUR SEAT IS NOT CONFIRMED | | Bob | YOUR SEAT IS CONFIRMED | | Mike | YOUR SEAT IS NOT CONFIRMED | | David | YOUR SEAT IS CONFIRMED | | Carol | YOUR SEAT IS NOT CONFIRMED | | James | YOUR SEAT IS CONFIRMED | +-------+----------------------------+ 6 rows in set (0.00 sec)
