我有一個主表（建議）和兩個參考表（專家，朋友）
advices
----------------------------------------
|id | advisor_id | advisor_type |
----------------------------------------
| 1 | 6 | expert |
| 2 | 6 | friend |
| 3 | 7 | expert |
| 4 | 8 | expert |
----------------------------------------
expert
----------------------------------
|id | lastname | firstname |
----------------------------------
| 6 | Polo | Marco |
| 7 | Wayne | John |
| 8 | Smith | Brad |
----------------------------------
friend
----------------------------------
|id | lastname | firstname |
----------------------------------
| 6 | Doe | John |
| 7 | Brown | Jerry |
| 8 | Goofy | Doofy |
----------------------------------
我想獲得所有建議（有些來自專家，有些來自朋友），并將他們各自的姓氏和名字作為結果集的一部分。
每個建議行都有通過 id 和型別與其關聯的參考表（專家表、朋友表）。
所以我想有一個基于 id 的結果，但取決于查詢哪個表的型別
結果看起來像這樣 結合參考表中的姓氏和名字，取決于它是專家還是朋友。
advices (array)
----------------------------------------------------------------
|id | advisor_id | advisor_type | lastname | firstname |
-----------------------------------------------------------------
| 1 | 6 | expert | Polo | Marco |
| 2 | 6 | friend | Doe | John |
| 3 | 7 | expert | Wayne | John |
| 4 | 8 | expert | Smith | Brown |
-----------------------------------------------------------------
在非編程的簡單單詞術語中，我想創建一個這樣的查詢。
SELECT
advices.id, advices.advisor_id, advices.type
IF advices.type==expert THEN expert.lastname, expert.firstname
ELSE IF advices.type==friend THEN friend.lastname, friend.firstname
FROM advices, expert, friend
顯然我知道 SELECT 陳述句不允許這種型別的動態邏輯。但這可以用另一種方式完成嗎？
uj5u.com熱心網友回復：
這應該有效：
SELECT a.*, e.firstname, e.lastname
FROM advices AS a
INNER JOIN expert AS e ON a.advisor_id = e.id AND a.advisor_type = 'expert'
UNION
SELECT a.*, f.firstname, f.lastname
FROM advices AS a
INNER JOIN friend AS f ON a.advisor_id = f.id AND a.advisor_type = 'friend'
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/331487.html