MySQL 問題相遇小記

小記一下今天的遇過的小題
分別兩道問題

第一題,先建立測試資料表

create table staff (
  id varchar(5) primary key,
  first_name varchar(20),
  last_name varchar(20),
  gender char(1)
);

insert into staff values ("S0001", "Tam",  "Peter", "M");
insert into staff values ("S0002", "Chen", "Tom",   "M");
insert into staff values ("S0003", "Law",  "May",   "F");
insert into staff values ("S0004", "Wong", "Cat",   "F");
insert into staff values ("S0005", "Sun",  "Yang",  "M");

問題: 輸出兩個欄位分別是 staff.id, Mr/Ms last_name first_name 的格式

解題

-- 方法一
SELECT id, CONCAT(
   (
     SELECT CASE gender
       WHEN "M" THEN "Mr"
       WHEN "F" THEN "Ms"
     END
   ), " ", last_name, " ", first_name)
FROM staff;

-- 方法二
SELECT id, CONCAT(
   (
     SELECT CASE
       WHEN gender = "M" THEN "Mr"
       WHEN gender = "F" THEN "Ms"
     END
   ), " ", last_name, " ", first_name)
FROM staff;

第二道問題,建立測試資料

create table staff (
  id varchar(5) primary key,
  first_name varchar(20),
  last_name varchar(20),
  gender char(1),
  position_id tinyint(1)
);

create table position (
  id varchar(5) primary key,
  name varchar(20)
);

insert into staff values ("S0001", "Tam",  "Peter", "M", 1);
insert into staff values ("S0002", "Chen", "Tom",   "M", 2);
insert into staff values ("S0003", "Law",  "May",   "F", 3);
insert into staff values ("S0004", "Wong", "Cat",   "F", 4);
insert into staff values ("S0005", "Sun",  "Yang",  "M", 3);

insert into position values (1, "Manager");
insert into position values (2, "Senior Programmer");
insert into position values (3, "Programmer");
insert into position values (4, "Account");

問題: 輸出兩個欄位分別是 Position.name, 每個職位的人數

解題

SELECT name, COUNT(*)
FROM staff s
LEFT JOIN position p ON s.position_id = p.id
group by (name)