sql - 在 SQL Server 中将一个表连接到两个一对多关系表

Question

friend 们，周五快乐

我正在尝试编写一个 SSRS 报告，显示来自三个(实际上大约 12 个，但只有三个相关的)表的数据，这些表具有 akward 关系，数据背后的 SQL 查询被证明是困难的。

涉及三个实体 - 采购订单、销售订单和交货单。问题是采购订单可以有很多销售订单，还有很多未链接到销售订单的交货……这太容易了。

销售订单表和交货表都可以通过外键和一个中间表链接到采购订单表。

我基本上需要列出采购订单、销售订单列表和它们旁边的交货列表，任何无效字段都为 NULL，以便在 SSRS 中提供所需的输出/当被读取时人工，即具有 2 个销售订单和 4 个交货日期的采购订单；

PO    SO      Delivery
1234  ABC     05/10
1234  DEF     09/10
1234  NULL    10/12
1234  NULL    14/12

以上(按 PO 分组时)将告诉用户有两个销售订单和四个(未链接的)交货日期。

同样，如果 SO 多于交付，我们需要在 Delivery 列中使用 NULL；

PO    SO      Delivery
1234  ABC     03/08
1234  DEF     NULL
1234  GHI     NULL
1234  JKL     NULL  

以上是 4 个 SO 和一个交付日期的情况。

单独使用左外连接会产生太多重复 - 在本例中为 8 行，因为它为销售订单上的每个匹配项提供 4 个交货日期；

PO    SO      Delivery
1234  ABC     05/10
1234  ABC     09/10
1234  ABC     10/12
1234  ABC     14/12    
1234  DEF     05/10
1234  DEF     09/10
1234  DEF     10/12
1234  DEF     14/12

复制 PO 列很好，因为 SSRS 可以直观地对其进行分组 - 但不能允许 SO/Delivery 字段重复，因为这不能在报告中删除 - 如果我将列分组在SO 的 SSRS 然后它仍然为每个吐出 4 个交货日期。

我们的查询唯一有效的情况是每个 PO 只有一个 SO。在那种情况下，单个 PO 和 SO 编号对于 x 次交付一起复制，并且可以在 SSRS 中整齐地分组。不幸的是，这在数据中很少见。

我曾想过尝试使用某种窗口函数或交叉应用，但两者都失败了，因为它们会对列出的每个 PO 编号重复，并最终吐出太多数据。

考虑到这还不够基于集合，无法在 SQL 中执行，我知道数据很糟糕..

非常感谢任何帮助。

编辑 - 表模式的基本 sqlfiddle 链接。省略了许多不相关的列。 http://sqlfiddle.com/#!2/5ba16

示例数据...

Purchase Order
PO_Number Style
1001      Black work boots
1002      Green hat
1006      Red Scarf

Sales Order
Sales_order_number    PO_number    Qty   Retailer
A100-21               1001         15    Walmart
A100-22               1001         29    Walmart
A200-31               1006         1000  Asda

Delivery
Delivery_ID     Delivery_Date    PO_number
1543285         10/05/2014       1001
1543286         12/05/2014       1001
1543287         17/05/2014       1001
1543288         21/05/2014       1002

Answer 1

如果您将行号分配给销售订单和交货单中的元素，您可以在其上进行链接。

像这样

declare @salesorders table (po int, so varchar(10))
declare @deliveries table (po int, delivery date)
declare @purchaseorders table (po int)
insert @purchaseorders values (123),(456)

insert @salesorders values (123,'a'),(123,'b'),(456,'c')
insert @deliveries values (123,'2014-1-1'),(456,'2014-2-1'),(456,'2014-2-1')


select *
from
(   
    select numbers.number, p.po, so.so, d.delivery from @purchaseorders p
    cross join (Select number from master..spt_values where type='p') numbers           
    left join (select *,ROW_NUMBER() over (partition by po order by so) sor  from @salesorders ) so
        on p.po = so.po and numbers.number = so.sor
    left join (select * , ROW_NUMBER() over (partition by po order by delivery) dor from @deliveries) d
        on p.po = d.po and numbers.number = d.dor
) v
where so is not null or delivery is not null
order by po,number