从您的示例中看来,Bond
和Job
表中没有匹配的记录。如果有匹配的记录,您将会得到如下结果:
BOND PO JOB PROJECT ORDNBR BILLOFLADINGNBR
Some bond BTBD000063 BTBD000072
4.158 Kingsport BTBD000063 BTBD000072
Some job BTBD000063 BTBD000072
82001-8177-44 BTBD000063 BTBD000072
IME-81-1(118) BTBD000063 BTBD000072
您查询结果集似乎很“稀疏”,因为您直接将OrderItemNumbers
表连接到主查询中:该表对应于每个子表(PONumbers
、Job
、Project
)的不同行,因此您得到了每个连接的独立行。为了避免这种情况,您可以将OrderItemNumbers
表与每个子表进行连接,然后通过共享的LoadOWID
将此子查询与OrderDetail
表连接:
SELECT
ISNULL(b.Descr, '') as Bond,
ISNULL(PO.Description, '') as PO,
ISNULL(j.Descr, '') as Job,
ISNULL(Proj.Descr, '') as Project,
d.OrdNbr,
d.BillofLadingNbr,
d.TripAndDeliveryTicketNbr
FROM
OrderDetail d
LEFT JOIN (
SELECT aux.*, n.*
FROM Bond aux INNER JOIN OrderItemNumbers n ON n.NumberOWID = aux.OWID AND n.NumType = 0
) AS b
ON b.LoadOWID = d.OWID
LEFT JOIN (
SELECT aux.*, n.*
FROM PONumbers aux INNER JOIN OrderItemNumbers n ON n.NumberOWID = aux.OWID AND n.NumType = 1
) AS PO
ON PO.LoadOWID = d.OWID
LEFT JOIN (
SELECT aux.*, n.*
FROM Job aux INNER JOIN OrderItemNumbers n ON n.NumberOWID = aux.OWID AND n.NumType = 2
) AS j
ON j.LoadOWID = d.OWID
LEFT JOIN (
SELECT aux.*, n.*
FROM Project aux INNER JOIN OrderItemNumbers n ON n.NumberOWID = aux.OWID AND n.NumType = 3
) AS Proj
ON Proj.LoadOWID = d.OWID
WHERE
d.OWID = 'BTBD1004'
那么你将获得以下结果:
Bond PO Job Project OrdNbr BillofLadingNbr
Some bond 4.158 Kingsport Some job 82001-8177-44 BTBD000063 BTBD000072
Some bond 4.158 Kingsport Some job IME-81-1(118) BTBD000063 BTBD000072
我同意,这并不完全符合您的要求。您似乎还需要在Project
表上进行某种形式的“部分旋转”(至少如此)。
理论上也是可能的,但我不会这样做。这显然是一个演示需求,在SQL层实现它的成本太高了。
编辑
为了获得完整的扁平结果集,可以这样做(基于这个SO答案):
DECLARE @bonds VARCHAR(MAX)
DECLARE @numbers VARCHAR(MAX)
DECLARE @jobs VARCHAR(MAX)
DECLARE @projs VARCHAR(MAX)
DECLARE @owid VARCHAR(10) = 'BTBD1004'
SELECT
@bonds = COALESCE(@bonds + ', ', '') + aux.Descr
FROM
Bond aux
INNER JOIN OrderItemNumbers n
ON n.NumberOWID = aux.OWID AND n.NumType = 0
WHERE
n.LoadOWID = @owid
SELECT
@numbers = COALESCE(@numbers + ', ', '') + aux.Description
FROM
PONumbers aux
INNER JOIN OrderItemNumbers n
ON n.NumberOWID = aux.OWID AND n.NumType = 1
WHERE
n.LoadOWID = @owid
SELECT
@jobs = COALESCE(@jobs + ', ', '') + aux.Descr
FROM
Job aux
INNER JOIN OrderItemNumbers n
ON n.NumberOWID = aux.OWID AND n.NumType = 2
WHERE
n.LoadOWID = @owid
SELECT
@projs = COALESCE(@projs + ', ', '') + aux.Descr
FROM
Project aux
INNER JOIN OrderItemNumbers n
ON n.NumberOWID = aux.OWID AND n.NumType = 3
WHERE
n.LoadOWID = @owid
SELECT
@bonds,
@numbers,
@jobs,
@projs,
d.OrdNbr,
d.BillofLadingNbr,
d.TripAndDeliveryTicketNbr
FROM
OrderDetail d
WHERE
d.OWID = @owid
这将导致以下结果:
Bond PO Job Project OrdNbr BillofLadingNbr
Some bond 4.158 Kingsport Some job 82001-8177-44, IME-81-1(118) BTBD000063 BTBD000072
如果您需要为同一个OWID
获取多个债券、多个PO和多份工作,它就能正常工作。
在我看来,这很好地说明了我想说的:是的,它能满足您的需求,但它非常丑陋。当然,如果您不在意,那就继续使用吧(只要添加一些触发器,您会感觉自己回到了1995年 :P)。
希望有所帮助。