假設現在有兩張表
CREATE TABLE class
(id int(3),
Name char(50),
floor int(3));
INSERT INTO class (id, Name, floor)
VALUES (1, '梅班', 1),(2, '葵班', 2),(3, '菊班', 3);
CREATE TABLE teacher
(id int(3),
Name char(50),
class_id int(3));
INSERT INTO teacher (id, Name, class_id)
VALUES (0, '小趙老師', 1),(1, '小王老師', 2),(2, '小光老師', 9);
join 後
select a.id, CONCAT(a.Name,'-',b.Name) as `Name`, b.id as class_id
from teacher a LEFT OUTER JOIN class b ON a.class_id = b.id;
有些欄位會是 null ,我可以根據欄位來把 null 轉成別的字串嗎?
比如 Name 欄位如果是 null 一律改成 '不分班'
class_id 欄位如果是 null 一律改成數字 0
線上結構表如下
http://sqlfiddle.com/#!9/62e64f/1
我希望的結果是
id | Name | class_id |
---|---|---|
0 | 小趙老師-梅班 | 1 |
1 | 小王老師-葵班 | 2 |
2 | 小光老師-不分班 | 0 |
請教各位大大要達成這樣的效果應該要怎樣做呢?有什麼關鍵字能查嗎?還是需要換別的 join 方法呢?
DB 是 mariadb 10
IFNULL函式: IFNULL(欄位值, 欄位值為Null時的值)
SELECT a.id,
CONCAT(a.Name,'-',IFNULL(b.Name, '不分班')) as `Name`,
IFNULL(b.id, '0') as class_id
FROM teacher a
LEFT OUTER JOIN class b
ON a.class_id = b.id;
SELECT *,
CASE WHEN Name IS NULL THEN '不分班' END AS Name,
CASE WHEN class_id IS NULL THEN 0 END AS class_id
FROM TABLENAME
若class_id 不為int 則改為 CASE WHEN class_id IS NULL THEN '0' END AS class_id
SELECT *,
ISNULL(Name,'不分班') AS Name,
ISNULL(class_id,0) AS Class_id
FROM TABLENAME
若class_id 不為int 則改為 ISNULL(class_id,'0') AS Class_id
SELECT A.id,ISNULL(A.Name,'不分班')Name,
ISNULL(A.class_id,0)class_id
FROM (
select a.id,
CONCAT(a.Name,'-',b.Name) as `Name`,
b.id as class_id
from teacher a
LEFT OUTER JOIN class b ON a.class_id = b.id) A
兩個方法給您參考