期望得到名稱而不是以黃色突出顯示的代碼。
員工表
CREATE TABLE [dbo].[_Employees](
[Name] [nvarchar](50) NULL,
[Code] [nvarchar](50) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[_Employees] ([Name], [Code]) VALUES (N'A', N'1')
GO
INSERT [dbo].[_Employees] ([Name], [Code]) VALUES (N'B', N'2')
GO
INSERT [dbo].[_Employees] ([Name], [Code]) VALUES (N'C', N'3')
GO
資料表
CREATE TABLE [dbo].[_Details](
[Department] [nvarchar](50) NULL,
[Zone] [nvarchar](50) NULL,
[Place] [nvarchar](50) NULL,
[City] [nvarchar](50) NULL,
[L1] [nchar](10) NULL,
[L2] [nchar](10) NULL,
[L3] [nchar](10) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[_Details] ([Department], [Zone], [Place], [City], [L1], [L2], [L3]) VALUES (N'Department1', N'Zone1', N'Place1', N'City1', N'1 ', N'2 ', N'3 ')
GO
INSERT [dbo].[_Details] ([Department], [Zone], [Place], [City], [L1], [L2], [L3]) VALUES (N'Department2', N'Zone2', N'Place2', N'City2', N'3 ', N'2 ', N'1 ')
GO
INSERT [dbo].[_Details] ([Department], [Zone], [Place], [City], [L1], [L2], [L3]) VALUES (N'Department3', N'Zone3', N'Place3', N'City3', N'2 ', N'3 ', N'1 ')
GO
我的嘗試
WITH _Details AS
(
SELECT L1 FROM _Details
UNION ALL
SELECT e.Name FROM _Employees e INNER JOIN _Details d ON e.Code = d.L1
)
SELECT * FROM _Details
不知道我做錯了什么？
uj5u.com熱心網友回復：
簡單JOIN的表格：
SELECT
d.[Department], d.[Zone], d.[Place], d.[City],
e1.[Name] AS L1, e2.[Name] AS L2, e3.[Name] AS L3
FROM _Details d
LEFT JOIN _Employees e1 ON d.L1 = e1.Code
LEFT JOIN _Employees e2 ON d.L2 = e2.Code
LEFT JOIN _Employees e3 ON d.L3 = e3.Code
uj5u.com熱心網友回復：
你只需要加入。三次加入員工表，每個員工列一次。由于 L1、L2 和 L3 可以為空，因此您需要外連接。
select
d.[Department], d.[Zone], d.[Place], d.[City],
e1.[Name] as name1,
e2.[Name] as name2,
e3.[Name] as name3
from [dbo].[_Details] d
left join [dbo].[_Employees] e1 on e1.[Code] = d.[L1]
left join [dbo].[_Employees] e2 on e2.[Code] = d.[L3]
left join [dbo].[_Employees] e3 on e3.[Code] = d.[L2];
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/454760.html
下一篇：比較2列并在SQL中回傳文本