我在简洁地描述我需要的内容时遇到了麻烦，因此感谢您对标题的任何帮助！
我有3张桌子：
Jobs
JobID
JobsDetail
DepartmentsID
Departments
Departments 记录数少（〜10）
如果我有10个Department行，那么我需要为每条Jobs记录重复所有10行。不在JobsDetail作业记录中的DepartmentID将NULL在“作业”列中显示一个值。有了一张Jobs唱片，典型的LEFT JOIN作品就能达到我想要的方式：
NULL
LEFT JOIN
Select d.Department, jobs.JobIdentifier From Departments d LEFT JOIN (Select Distinct j.JobID, j.JobIdentifier, DepartmentID, From Jobs j Join JobsDetail jd on j.JobID = jd.JobID) jobs on d.DepartmentID = jobs.DepartmentID
产生以下结果：
Department JobIdentifier 310 NULL 320 NULL 430 NULL 450 NULL 460 NULL 500 NULL 530 1000 533 1000 534 1000 535 NULL
当然，当添加另一个Jobs记录时，我看到以下内容：
Department JobIdentifier 310 2000 320 NULL 430 NULL 450 NULL 460 2000 500 NULL 530 1000 533 1000 534 1000 534 2000 535 NULL
我需要的是这样的：
Department JobIdentifier 310 NULL 320 NULL 430 NULL 450 NULL 460 NULL 500 NULL 530 1000 533 1000 534 1000 535 NULL 310 2000 320 NULL 430 NULL 450 NULL 460 2000 500 NULL 530 NULL 533 NULL 534 2000 535 NULL
如何做到这一点？
这是表中的一些测试数据：
JobID SubPlantID JobIdentifier PartFamilyID OrderDate OrderedBy OrderQuantity DueDate SpecialInstructions PrintDate PrintedBy StartDate StartedBy ProducedQuantity ReprintNumber CompletedDate Location -------------------- ----------- -------------------------------------------------- ------------ ----------------------- -------------------------------------------------- ------------- ----------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------- -------------------------------------------------- ----------------------- -------------------------------------------------- ---------------- ------------- ----------------------- -------------------------------------------------- 2 1 1000 1 2012-12-21 13:20:00.000 Keith 1 2012-12-28 00:00:00.000 NULL NULL 2012-12-28 00:00:00.000 NULL NULL 0 NULL NULL 3 1 2000 1 2013-01-03 00:00:00.000 Jon 10 2013-01-10 00:00:00.000 NULL NULL NULL NULL NULL NULL 0 NULL NULL JobsDetailID JobID Operation FirstStartDate OperationQuantity OperationStatusTypeID OperationDescription DepartmentID WorkCenterID UserName -------------------- -------------------- --------- ----------------------- ----------------- --------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------ ------------ -------------------------------------------------- 1 2 10 NULL 1 2 Weld 1 2 keith 2 2 20 NULL 1 3 Punch 1 1 keith 3 2 30 NULL 1 2 Form 3 5 jon 4 2 40 NULL 1 3 Paint 2 4 jon 6 2 50 NULL 1 3 Glue 2 4 jwood 9 2 60 NULL 1 2 Eat 3 5 jon 10 3 20 NULL 10 1 Punch 10 18 Jon Wrye 11 3 30 NULL 10 0 Form 15 29 Jon Wrye 12 3 40 NULL 10 0 Weld 13 24 Jon Wrye 13 3 10 NULL 10 2 Start 1 1 jwrye 14 3 50 NULL 10 0 Finish 1 2 jwrye DepartmentID Department ------------ -------------------------------------------------- 1 534 2 533 3 530 4 535 5 500 6 450 7 430 8 200 9 240 10 232 11 220 12 300 13 460 14 320 15 310
您应该从工作和部门之间的交叉联接开始，然后从那里建立查询：
Select d.Department, jobs.JobIdentifier From Departments d cross join (select distinct JobIdentifier from Jobs j) ji LEFT JOIN (Select Distinct j.JobID, j.JobIdentifier, DepartmentID, From Jobs j Join JobsDetail jd on j.JobID = jd.JobID ) jobs on d.DepartmentID = jobs.DepartmentID and ji.jobidentifer = jobs.jobIDentifier
我对JobId和JobIdentifier之间的区别还不清楚，所以这可能不太正确。
如果将ji.JobIdentifier添加到select列表中，即使没有匹配项，您也会看到部门 应该 属于哪个工作。
select
