SQL 将一个表中的多列连接到另一表中的单列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/769953/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Joining multiple columns in one table to a single column in another table
提问by Steve Syfuhs
I am looking to create a view that pulls data from two tables "Schedule" and "Reference".
我希望创建一个视图，从两个表“Schedule”和“Reference”中提取数据。
Schedule has 50+ columns (it's almost completely denormalized -- not my design), most of which contain a value that could be joined to a column in the Reference table.
Schedule 有 50 多列（它几乎完全非规范化——不是我的设计），其中大部分包含一个可以连接到 Reference 表中的列的值。
How do I write the SQL statement to correctly join each column in Schedules to the single column in Reference?
如何编写 SQL 语句以将 Schedules 中的每一列正确连接到 Reference 中的单个列？
The Schedule table is defined as:
Schedule 表定义为：
CREATE TABLE [dbo].[Schedule](
[ID] [int] NOT NULL,
[SCHEDULEWEEK] [datetime] NOT NULL,
[EMPNO] [numeric](10, 0) NOT NULL,
[EMPLNAME] [varchar](32) NULL,
[EMPFNAME] [varchar](32) NULL,
[EMPSENDATE] [datetime] NULL,
[EMPHIREDATE] [datetime] NULL,
[EMPTYPE] [char](1) NULL,
[EMPSTATUS] [char](1) NULL,
[SNREFUSALS] [tinyint] NULL,
[QUALSTRING] [varchar](128) NULL,
[JOBOVERSHIFTTYPE] [bit] NULL,
[SHORTNOTICE] [bit] NULL,
[SHORTNOTICEWAP] [bit] NULL,
[SHORTNOTICEPHONE] [varchar](32) NULL,
[LEADHAND] [bit] NULL,
[DUALCURRENCY] [bit] NULL,
[MIN100WINDOW] [bit] NULL,
[STATHOLIDAY] [bit] NULL,
[AREAOVERHOURS] [bit] NULL,
[DOUBLEINTERZONES] [bit] NULL,
[MAXDAYSPERWEEK] [tinyint] NULL,
[MAXHOURSPERWEEK] [numeric](10, 2) NULL,
[MAXHOURSPERSHIFT] [numeric](10, 2) NULL,
[MAXDOUBLESPERWEEK] [tinyint] NULL,
[ASSIGNEDDAYS] [tinyint] NULL,
[ASSIGNEDHOURS] [numeric](10, 2) NULL,
[ASSIGNEDDOUBLES] [tinyint] NULL,
[ASSIGNEDLOAHOURS] [numeric](10, 2) NULL,
[SHIFTNO1] [int] NULL,
[TEXT1_1] [varchar](64) NULL,
[TEXT2_1] [varchar](64) NULL,
[DAYFLAG1] [bit] NULL,
[COMMENT1] [text] NULL,
[SHIFTNO2] [int] NULL,
[TEXT1_2] [varchar](64) NULL,
[TEXT2_2] [varchar](64) NULL,
[DAYFLAG2] [bit] NULL,
[COMMENT2] [text] NULL,
[SHIFTNO3] [int] NULL,
[TEXT1_3] [varchar](64) NULL,
[TEXT2_3] [varchar](64) NULL,
[DAYFLAG3] [bit] NULL,
[COMMENT3] [text] NULL,
[SHIFTNO4] [int] NULL,
[TEXT1_4] [varchar](64) NULL,
[TEXT2_4] [varchar](64) NULL,
[DAYFLAG4] [bit] NULL,
[COMMENT4] [text] NULL,
[SHIFTNO5] [int] NULL,
[TEXT1_5] [varchar](64) NULL,
[TEXT2_5] [varchar](64) NULL,
[DAYFLAG5] [bit] NULL,
[COMMENT5] [text] NULL,
[SHIFTNO6] [int] NULL,
[TEXT1_6] [varchar](64) NULL,
[TEXT2_6] [varchar](64) NULL,
[DAYFLAG6] [bit] NULL,
[COMMENT6] [text] NULL
-- Snip
) ON [PRIMARY]
And the Reference table is defined as:
参考表定义为：
CREATE TABLE [dbo].[Reference](
[ID] [int] NOT NULL,
[CODE] [varchar](21) NOT NULL,
[LOCATIONCODE] [varchar](4) NOT NULL,
[SCHAREACODE] [varchar](16) NOT NULL,
[LOCATIONNAME] [varchar](32) NOT NULL,
[FLTAREACODE] [varchar](16) NOT NULL
) ON [PRIMARY]
I am trying to join each [TEXT1_]/[TEXT2_] column in Schedule to the [SCHAREACODE] column in reference. All the reference table contains is a list of areas where the employee could work.
我正在尝试将附表中的每个 [TEXT1_ ]/[TEXT2_] 列加入参考中的 [SCHAREACODE] 列。所有参考表包含的是员工可以工作的区域列表。
回答by TheSoftwareJedi
I think he means to join on the Reference table multiple times:
我认为他的意思是多次加入 Reference 表：
SELECT *
FROM Schedule AS S
INNER JOIN Reference AS R1
ON R1.ID = S.FirstID
INNER JOIN Reference AS R2
ON R2.ID = S.SecondID
INNER JOIN Reference AS R3
ON R3.ID = S.ThirdID
INNER JOIN Reference AS R4
ON R4.ID = S.ForthID
回答by Mark Brackett
Your description is a bit lacking, so I'm going to assume that
你的描述有点欠缺，所以我假设
Schedule has 50+ columns (it's almost completely denormalized -- not my design), most of which contain a value that could be joined to a column in the Reference table.
Schedule 有 50 多列（它几乎完全非规范化——不是我的设计），其中大部分包含一个可以连接到 Reference 表中的列的值。
means that 1 of the 50+ columns in Schedule is a ReferenceId. So, given a table design like:
表示 Schedule 中的 50 多列中有 1 列是 ReferenceId。所以，给定一个表格设计，如：
Schedule ( MaybeReferenceId1, MaybeReferenceId2, MaybeReferenceId3, ... )
Reference ( ReferenceId )
Something like:
就像是：
SELECT *
FROM Schedule
JOIN Reference ON
Schedule.MaybeReferenceId1 = Reference.ReferenceId
OR Schedule.MaybeReferenceId2 = Reference.ReferenceId
OR Schedule.MaybeReferenceId3 = Reference.ReferenceId
OR Schedule.MaybeReferenceId4 = Reference.ReferenceId
...
would work. You could simplify it by using INif your RDBMS supports it:
会工作。IN如果您的 RDBMS 支持，您可以通过使用来简化它：
SELECT *
FROM Schedule
JOIN Reference ON
Reference.ReferenceId IN (
Schedule.MaybeReferenceId1,
Schedule.MaybeReferenceId2,
Schedule.MaybeReferenceId3,
Schedule.MaybeReferenceId4,
...
)
回答by NickZoic
Agree with TheSoftwareJedi, but can I just suggest using LEFT JOINs so that failures-to-match don't cause your Schedule row to disappear?
同意TheSoftwareJedi，但我可以建议使用 LEFT JOIN 以便匹配失败不会导致您的 Schedule 行消失吗？
Of course, doing 28 JOINs is going to be a bit cumbersome whatever the details.
当然，无论细节如何，做 28 个 JOIN 都会有点麻烦。
I'm not sure I'd call this "denormalized", more "abnormalized" ... :-)
我不确定我会称之为“非规范化”，更“异常化”...... :-)
回答by Helgi
Try a query like this:
试试这样的查询：
select s.*, r.schareacode from schedule s,
where
s.text1_1 = s.schareacode
or s.text2_1 = s.schareacode
or s.textx_x = s.schareacode
..
You should be able to get the same results with traditional joins so I recommend you experiment with that as well.
您应该能够通过传统连接获得相同的结果，因此我建议您也尝试一下。
回答by beach
From updated question
来自更新的问题
Perhaps something like this? It will be messy no matter what you do.
也许像这样？不管你做什么都会很乱。
SELECT S.ID
S.TEXT1_1,
TEXT1_1_RID = COALESCE((SELECT MAX(R.ID) FROM Reference R WHERE R.SCHAREACODE = S.TEXT1_1), 0),
S.TEXT1_2,
TEXT1_2_RID = COALESCE((SELECT MAX(R.ID) FROM Reference R WHERE R.SCHAREACODE = S.TEXT1_2), 0),
...
FROM Schedule S
