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

Question

大家好，周五快乐。

我正在尝试编写一个SSRS报告，显示来自三个表（实际上有约12个表，但只涉及其中三个）的数据，它们之间的关联关系很棘手，该数据背后的SQL查询变得非常困难。

这里涉及三个实体 - 采购订单、销售订单和交付。问题在于采购订单可以拥有多个销售订单，并且还可能有许多与销售订单无关联的交货日期......这样做太容易了。

销售订单和交货日期两个表格都可以通过外键和各自的中间表格与采购订单表格相关联。

我需要列出采购订单、销售订单列表和相应的交货日期列表，对于任何不合法的字段，将其置空，以便在SSRS/人类阅读时产生所需的输出，例如，对于具有2个销售订单和4个交货日期的采购订单；

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

以上内容（按PO分组）将向用户显示有两个销售订单和四个（未关联）交货日期。

同样，如果销售订单（SOs）数量大于交货（deliveries）数量，则需要在交货列中使用NULL值；

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

如果有4个销售订单和一个交货日期，那么使用左外连接会生成太多的重复数据 - 在这种情况下，它会为每个销售订单的匹配值生成4个交货日期，因此会生成8行数据。

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/交货字段不能重复，因为在报表中无法消除重复，如果我按SO在SSRS中对列进行分组，那么每个SO会产生4个交货日期。
我们的查询只在PO下只有一个SO时才有效。在这种情况下，单个PO和SO号码将重复X次交付，并且可以在SSRS中整齐地分组。不幸的是，这在数据中很少发生。
我想过尝试使用某种窗口函数或CROSS APPLY，但两者都失败了，因为它们将重复列出每个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

- alexstuart

---

你能否在 SQLFiddle 上模拟你的表格？（请注意，DDL 有限制，最多8000个字符）否则，请提供每个表格数据的简单示例（不是连接结果）。 - Paul Maxwell

这个有用吗？- http://sqlfiddle.com/#!2/5ba16 - 每个表中都有更多的数据，但所有数据都是特定于各个表格（即与交接无关的各种交付相关日期，有关所购买产品的各种信息）。 - alexstuart

这是一些非常必要的数据样本。 - Paul Maxwell

在原帖中添加了一些数据。谢谢。 - alexstuart

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          

- podiluska

---

感谢podiluska - 真是太棒了。我采用了你的主题并应用到我的表格上（它们都有许多非关键行需要包括），最终使其工作。本来想投票的，但需要15个声望，而我刚刚注册了stackexchange（以前从未提出过问题！）。 - alexstuart