想請教各位大大，不曉得以下做法有沒有辦法達到
資料表
Item
Class
想要的結果
關聯圖
Item 的 Item_Type_ID 跟 Item_Brand_ID 都要關連 Class_ID
有沒有辦法重複對同個資料表重複關連不同的欄位
我目前的作法是在程式中建立兩個 Item_Type 跟 Item_Brand 的 class 分兩次查詢產生資料後再抓取用 Class_Key 去做判別
SELECT * FROM Class WHERE Item_Key = 'Item_Type' AND Class_ID = + Item_Type_ID
SELECT * FROM Class WHERE Item_Key = 'Item_Brand' AND Class_ID = + Item_Brand_ID
想知道有可以一段語法就撈出想要的資料的方法嗎？謝謝！
已邀請的邦友 {{ invite_list.length }}/5
Left Join兩次哩 ?
Select a.Item_ID, a.Item_Name, a.Item_Type_ID, IFNULL(b.Class_Name, '') as Item_Type_Name
, a.Item_Brand_ID, IFNULL(c.Class_Name, '') as Item_Brand_Name
from Item a
Left Join Class b on b.Class_Key = 'Item_Type' and a.Item_Type_ID = b.Class_ID
Left Join Class c on c.Class_Key = 'Item_Brand' and a.Item_Brand_ID = c.Class_ID
結果會像下面這樣，不過Itme_ID=2、1 的好像不太對?
改子查詢
declare @Class table(
Class_Auto_ID int
,Class_Key nvarchar(50)
,Class_ID nvarchar(50)
,Class_Name nvarchar(50)
)
declare @Item table(
Item_ID int
,Item_Name int
,Item_Type_ID nvarchar(50)
,Item_Brand_ID nvarchar(50)
)
insert into @Class
values(1,'Item_Type','A','AA')
,(3,'Item_Brand','AA','AA')
,(2,'Item_Type','B','BB')
insert into @Item
values(2,2,'','AA')
,(4,4,'','')
,(1,1,'A','')
,(3,3,'B','AA')
SELECT Item_ID
,Item_Name
,Item_Type_ID
,isNull((select top 1 Class_Name from @Class where Item_Name = Class_Auto_ID and Class_ID = Item_Type_ID ),'') Item_Type_Name
,Item_Brand_ID
,isNull((select top 1 Class_Name from @Class where Item_Name = Class_Auto_ID and Class_ID = Item_Brand_ID ),'') Item_Brand_Name
FROM @Item
Create table Class (
Class_Auto_ID int,
Class_Key nvarchar(30),
Class_ID nvarchar(30),
Class_Name nvarchar(30));
insert into Class values
(1,'Item_Type','A','A'),
(3,'Item_Brand','AA','AA'),
(2,'Item_Type','B','B');
Create table Item (
Item_ID int,
Item_Name int,
Item_Type_ID nvarchar(30),
Item_Brand_ID nvarchar(30));
insert into Item values
(2,2,'','AA'),
(4,4,'',''),
(1,1,'A',''),
(3,3,'B','AA');
Select M.Item_ID,M.Item_Name,M.Item_Type_ID,
case When (R.Class_Key = 'Item_Type') and (Q.Class_Key = 'Item_Brand') then R.Class_Name else '' end as Item_Type_Name,
M.Item_Brand_ID,
case When (R.Class_Key = 'Item_Type') and (Q.Class_Key = 'Item_Brand') then Q.Class_Name else '' end as Item_Brand_Name
From Item AS M
Left join Class As R on (R.Class_ID = M.Item_Type_ID) and (R.Class_Key = 'Item_Type')
Left join Class As Q on (Q.Class_ID = M.Item_Brand_ID) and (Q.Class_Key = 'Item_Brand')
iThome鐵人賽
看影片追技術