mysql - 编写查询以获得所需的 SQL 表输出

Question

我有一些表，并使用 sql 查询在下表中找到所需的输出。表是:

EmpID   TabId
101   1
102   2
103   3
104   4
105   1

其中表名称为Master

EmpID   Name
101   A1
102   A2
103   A3
104   A4
105   A5

表名称为TableA

EmpID   Name
101   B1
102   B2
103   B3
104   B4
105   B5

表名称为TableB

EmpID   Name
101   C1
102   C2
103   C3
104   C4
105   C5

表名称为TableC

EmpID   Name
101   D1
102   D2
103   D3
104   D4
105   D5

表名称为TableD

TabId   Name
1     TableA
2     TableB
3     TableC
4     TableD

表名称为 AttMaster 已提供给我们，所需的输出如下

TabId   Name
101     A1
102     B2
103     C3
104     D4
105     A5

请帮助我如何通过 sql 查询获得所需的输出。我没有得到任何解决方案来找到输出。

Answer 1

这应该有效。希望对您有帮助

DECLARE @Master TABLE (EmpID INT, TabId INT)
DECLARE @TableA TABLE (EmpID INT, Name VARCHAR(10))
DECLARE @TableB TABLE (EmpID INT, Name VARCHAR(10))
DECLARE @TableC TABLE (EmpID INT, Name VARCHAR(10))
DECLARE @TableD TABLE (EmpID INT, Name VARCHAR(10))
DECLARE @AttMaster TABLE (TabId INT, Name VARCHAR(10))

INSERT INTO @Master VALUES
('101', '1'),
('102', '2'),
('103', '3'),
('104', '4'),
('105', '1')

INSERT INTO @TableA VALUES
('101', 'A1'),
('102', 'A2'),
('103', 'A3'),
('104', 'A4'),
('105', 'A5')

INSERT INTO @TableB VALUES
('101', 'B1'),
('102', 'B2'),
('103', 'B3'),
('104', 'B4'),
('105', 'B5')

INSERT INTO @TableC VALUES
('101', 'C1'),
('102', 'C2'),
('103', 'C3'),
('104', 'C4'),
('105', 'C5')

INSERT INTO @TableD VALUES
('101', 'D1'),
('102', 'D2'),
('103', 'D3'),
('104', 'D4'),
('105', 'D5')

INSERT INTO @AttMaster VALUES
('1', 'TableA'),
('2', 'TableB'),
('3', 'TableC'),
('4', 'TableD')


;WITH cte_MasterAttMaster AS
(
SELECT m.EmpID ,am.TabId, am.Name
FROM @Master m
JOIN @AttMaster am
on m.TabId = am.TabId
)
,cte_TableABCD(EmpID,Name,TName) AS
(
SELECT *,'TableA' FROM @TableA 
UNION ALL
SELECT *,'TableB' FROM @TableB 
UNION ALL
SELECT *,'TableC' FROM @TableC 
UNION ALL
SELECT *,'TableD' FROM @TableD
)
SELECT mam.EmpID,
      tb.Name
FROM cte_MasterAttMaster AS mam
    JOIN cte_TableABCD AS tb
    ON tb.EmpID = mam.EmpID
    AND tb.TName = mam.Name
ORDER BY mam.EmpID