SQL Select具有对单个表的多个引用 [英] SQL Select With Multiple References to Single Table

问题描述

我有一个包含多个关系表的SQL DB。主表中的某些字段多次引用另一个表。例如，假设我有一个销售员数据库，负责多个州的销售。我的数据库具有State1，State2和State3的字段，所有字段都映射回一个States表。我一生都不知道如何编写查询以返回具有所有枚举状态的记录。如果我只需要一个State字段，我就会知道：

I have a SQL DB that contains multiple relational tables. There are some fields in the master table that reference another table multiple times. For example, say I have a database of salesmen who are responsible of sales for multiple states. My database has fields for State1, State2, and State3 all of which map back to a States table. I can't figure out for the life of me how to write a query to return a record with all the enumerated states. If I only needed one State field, I know I would do:

SELECT Master.Name, State.Enumeration AS 'State'
FROM MasterTable Master, StateTable State
WHERE Master.State1 = State.ID;

如何为我的所有State字段扩展此字段？

How can I expand this for all my State fields?

谢谢。

推荐答案

将列从每个唯一联接返回到州：

Returning a column from each of the unique joins to the states:

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列：

Returning 1 column of all the states from the 3 joins:

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

s也是列查询...

There is also column-queries...

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

这篇关于SQL Select具有对单个表的多个引用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！