我有一个包含多个关系表的SQL DB。主表中的某些字段多次引用另一个表。例如,假设我有一个销售员数据库,负责多个州的销售。我的数据库具有State1,State2和State3的字段,所有字段都映射回一个States表。我一生都不知道如何编写查询以返回具有所有枚举状态的记录。如果我只需要一个State字段,我就会知道:
SELECT Master.Name, State.Enumeration AS 'State'
FROM MasterTable Master, StateTable State
WHERE Master.State1 = State.ID;
如何为我的所有“州”字段扩展此字段?
谢谢。
最佳答案
从每个唯一的联接返回一列到状态:
select m.Name, s1.Enumeration as State1, s2.Enumeration as State2, s3.Enumeration as State3
from MasterTable m
left join StateTable s1 on m.State1 = s1.ID
left join StateTable s2 on m.State2 = s2.ID
left join StateTable s3 on m.State3 = s3.ID
从3个联接中返回所有状态的1列:
select m.Name, ISNULL(s1.Enumeration + ',','')
+ ISNULL(s2.Enumeration + ',','')
+ ISNULL(s3.Enumeration,'') as Enumeration
from MasterTable m
left join StateTable s1 on m.State1 = s1.ID
left join StateTable s2 on m.State2 = s2.ID
left join StateTable s3 on m.State3 = s3.ID
也有列查询...
select m.Name,
ISNULL((select Enumeration from StateTable where ID = m.State1),'') as State1,
ISNULL((select Enumeration from StateTable where ID = m.State2),'') as State2,
ISNULL((select Enumeration from StateTable where ID = m.State3),'') as State3
from MasterTable m