CREATE TABLE #main
(
ID INT
, firstName VARCHAR(12)
);
CREATE TABLE #worker
(
ID INT
, WorkerID INT
);
CREATE TABLE #location
(
ID INT
, Location VARCHAR(12)
);
CREATE TABLE #middlename
(
ID INT
, MiddleName VARCHAR(12)
);
INSERT INTO #main
VALUES
(1, 'Jim')
, (2, 'John');
INSERT INTO #worker
VALUES
(1, 123)
, (1, 2345)
, (2, 4534);
INSERT INTO #location
VALUES
(1, 'Asia')
, (1, 'America')
, (1, 'Africa');
INSERT INTO #middlename
VALUES
(2, 'Jack');
如果您只是想根据您在评论中所说的内容加入表格,请执行以下操作 -
SELECT m.ID
, m.firstName
, w.WorkerID
, l.Location
, mn.MiddleName
FROM #main AS m
LEFT JOIN #worker AS w
ON w.ID = m.ID
LEFT JOIN #location AS l
ON l.ID = m.ID
LEFT JOIN #middlename AS mn
ON mn.ID = m.ID
WHERE m.ID = 1;
ID
名
工人ID
地点
中间名字
1
吉姆
123
亚洲
无效的
1
吉姆
123
美国
无效的
1
吉姆
123
非洲
无效的
1
吉姆
2345
亚洲
无效的
1
吉姆
2345
美国
无效的
1
吉姆
2345
非洲
无效的
但如果你想得到那个奇怪的结果,那就是垃圾,但是这样做 -
SELECT base.ID
, base.firstName
, IIF(base.ID = w.ID, w.WorkerID, NULL) AS WorkerID
, base.Location
, base.MiddleName
FROM
(
SELECT m.ID
, m.firstName
, l.Location
, mn.MiddleName
, ROW_NUMBER() OVER (PARTITION BY m.ID ORDER BY (SELECT NULL)) AS rn
FROM #main AS m
LEFT JOIN #location AS l
ON l.ID = m.ID
LEFT JOIN #middlename AS mn
ON mn.ID = m.ID
WHERE m.ID = 1
) AS base
INNER JOIN
(
SELECT ID
, WorkerID
, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn
FROM #worker
) AS w
ON base.rn = w.rn;
如果您只是想根据您在评论中所说的内容加入表格,请执行以下操作 -
但如果你想得到那个奇怪的结果,那就是垃圾,但是这样做 -
演示数据库小提琴