一個資料處理的情境.需要找出具備符合某種技能的人,
例如進行某個專案,需要有會Elixir或Erlang的人,
初看好似簡單,但是有些人也許同時具備這兩項技能,
這時候顯示的方式若能夠加以顯示出來,則對後續決策
較為方便.

先來建立測試Table

CREATE TABLE iron1002(
id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
person CHAR(10) NOT NULL,
skill CHAR(10) NOT NULL
);

INSERT INTO iron1002(person, skill) VALUES
('油達大師', 'Erlang'),
('MikuMiku', 'Elixir'),
('壞壞總裁', 'Elixir'),
('玉面飛鷹', 'PHP'),
('C3PO', 'C++'),
('MikuMiku', 'Erlang');


--方式1
--1.1
--先找出只具備其中一種技能的人.

SELECT person
     , skill
  FROM iron1002
 WHERE skill IN ('Elixir', 'Erlang')
 GROUP BY person
HAVING COUNT(DISTINCT skill) = 1;

+--------------+--------+
| person       | skill  |
+--------------+--------+
| 壞壞總裁     | Elixir |
| 油達大師     | Erlang |
+--------------+--------+

--1.2
--再找出同時具備兩種技能的人

SELECT person
     , '兩種都會' AS skill
  FROM iron1002
 WHERE skill IN ('Elixir', 'Erlang')
 GROUP BY person
HAVING COUNT(DISTINCT skill) = 2;

+----------+--------------+
| person   | skill        |
+----------+--------------+
| MikuMiku | 兩種都會     |
+----------+--------------+

-- 最終用UNION ALL結合起來.

SELECT person
     , '兩種都會' AS skill
  FROM iron1002
 WHERE skill IN ('Elixir', 'Erlang')
 GROUP BY person
HAVING COUNT(DISTINCT skill) = 2
 UNION ALL
SELECT person
     , skill
  FROM iron1002
 WHERE skill IN ('Elixir', 'Erlang')
 GROUP BY person
HAVING COUNT(DISTINCT skill) = 1;

+--------------+--------------+
| person       | skill        |
+--------------+--------------+
| MikuMiku     | 兩種都會     |
| 壞壞總裁     | Elixir       |
| 油達大師     | Erlang       |
+--------------+--------------+

******** 華麗的分隔線 ********
--方式2
--先過濾符合條件,再計算技能數目,透過if()判斷是否顯示為"兩種都會".
--if()是MySQL的,一般可用CASE判斷,效果一樣.

SELECT person
     , IF(COUNT(1) = 2, '兩種都會', skill) AS skill
  FROM iron1002
 WHERE skill IN ('Elixir', 'Erlang')
 GROUP BY person;

+--------------+--------------+
| person       | skill        |
+--------------+--------------+
| MikuMiku     | 兩種都會     |
| 壞壞總裁     | Elixir       |
| 油達大師     | Erlang       |
+--------------+--------------+

--- 普通分隔線 ---
SQL的運用是很靈活的,有時候直觀想到的方法,不一定是最好的方法,
我們可以多想想,靈機一動,想出更好的方式,那感覺是很美妙的.