MySQLでCASE WHEN文を使ってみる
1.CASE WHENで文字列置換処理
Drop table sales;
CREATE TABLE sales(
num MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(20),
winter INT,
spring INT,
summer INT,
fall INT,
category CHAR(13),
primary key(num)
)type=MyISAM;
insert into sales value(1, 'Java’, 1067 , 200, 150, 267,’Holiday’);
insert into sales value(2, 'C’,970,770,531,486,’Profession’);
insert into sales value(3, 'JavaScript’,53,13,21,856,’Literary’);
insert into sales value(4, 'SQL’,782,357,168,250,’Profession’);
insert into sales value(5, 'Oracle’,589,795,367,284,’Holiday’);
insert into sales value(6, 'MySQL’,953,582,336,489,’Literary’);
insert into sales value(7, 'Cplus’,752,657,259,478,’Literary’);
insert into sales value(8, 'Python’,67,23,83,543,’Holiday’);
insert into sales value(9, 'PHP’,673,48,625,52,’Profession’);
select * from sales;
SELECT name AS Name,
CASE category
WHEN “Holiday" THEN “Seasonal"
WHEN “Profession" THEN “Bi_annual"
WHEN “Literary" THEN “Random" END AS “Pattern"
FROM sales;
2.単純なSQL構文
SELECT CASE WHEN 10*2=30 THEN ’30 correct’
WHEN 10*2=40 THEN ’40 correct’
ELSE 'Should be 10*2=20’
END;
3.複数の表現式
SELECT CASE 10*2
WHEN 20 THEN ’20 correct’
WHEN 30 THEN ’30 correct’
WHEN 40 THEN ’40 correct’
END;
4.selectクエリにCASE WHENを使う
Drop table DVDs;
CREATE TABLE DVDs (
ID SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
Name VARCHAR(60) NOT NULL,
NumDisks TINYINT NOT NULL DEFAULT 1,
RatingID VARCHAR(4) NOT NULL,
StatID CHAR(3) NOT NULL
)
ENGINE=INNODB;
INSERT INTO DVDs (Name, NumDisks, RatingID, StatID)
VALUES ('Christmas’, 1, 'NR’, 's1’),
('Doc’, 1, 'G’, 's2’),
('Africa’, 1, 'PG’, 's1’),
('Falcon’, 1, 'NR’, 's2’),
('Amadeus’, 1, 'PG’, 's2’),
('Show’, 2, 'NR’, 's2’),
('View’, 1, 'NR’, 's1’),
('Mash’, 2, 'R’, 's2’);
SELECT Name, RatingID AS Rating,
CASE RatingID
WHEN 'R’ THEN 'Under 18 requires an adult.’
WHEN 'X’ THEN 'No one 18 and under.’
WHEN 'NR’ THEN 'Use discretion when renting.’
ELSE 'OK to rent to minors.’
END AS Policy
FROM DVDs
ORDER BY Name;