다음은 MySQL CASE OR 조건의 구문입니다.
SELECT yourColumnName1,.....N , CASE WHEN yourColumnName2=0 or yourColumnName2IS 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)
select 문을 사용하여 테이블의 모든 레코드를 표시합니다.
쿼리는 다음과 같습니다.
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)
다음은 OR 조건이 있는 MySQL CASE입니다.
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)