时间戳

首页 > 解决方案 > 如何进行高级 SQL 连接谓词/提示以避免不需要的行？

sql - 如何进行高级 SQL 连接谓词/提示以避免不需要的行？

问题描述

第一部分是对我正在处理的真实世界情况的描述，我正在构建一份关于库存互换的报告，其中我将与资产相关的报告表、包含资产详细信息的表和 RMA 请求表分开，但与哪个资产被其他资产替换，只是与报告订单相关联的 RMA 订单的模糊关系。

我有四张桌子。

所以记录是这样的。

table 1 are something like.
orderNo -  reported item 
1               1
1               2
2              56
2              34
2              23
3              15

table 2 
device id    lots of device detailed stuff in columns.
1              ...
2              ...
3              ...
...
15
23
34
56

table 3 (replacements)
rmaid   replacement
1           3
1           4
2           7
2           8
2           9
3           16

这是我试图解决的问题的一个例子，也是我的问题出现的地方。

create table report(
    ID  int  not null,
    itemID int );

    insert into report (ID, ItemID)
    values (1,1),(1,2),(2,56),(2,34),(2,23),(3,15)

    create table device(
    ID int not null,
    dev_detail nvarchar(50) 
    );

    insert into device (ID, dev_detail)
    values (1,'det1'),(2,'det2'),(3,'det3'),(4,'det4'),(5,'det5'),
        (6,'det6'),(7,'det7'),(8,'det8'),(9,'det9'),(15,'det15'),
        (16,'det16'),(23,'dev23'),(34,'det34'),(56,'det56')

    create table replacement(
    id  int not null,
    ItemID int );

    insert into replacement (ID, ItemID)
    values (1,3),(1,4),(2,7),(2,8),(2,9),(3,16)

    create table [report-replacement](
    ID int not null,
    reportID int,
    replaceID int);

    insert into [report-replacement] (ID, reportID, replaceID)
    values (1,1,1),(2,2,2),(3,3,3)

    select 
    rep.ID as report_id,
    rep.itemID as reported_item,
    rep.dev_detail as reported_item_detail,
    rep.replaceID as report_replace_id,
    rma.id as rma_id,
    rma.ItemID as rma_item,
    rma.dev_detail as rma_item_detail

    from
    (
      select report.ID,report.itemID,device.dev_detail, replaceID
      from 
       report 
        inner join device on report.itemID = device.ID
        inner join [report-replacement] on reportID = [report-replacement].reportID
    ) as Rep
    inner join
    (
      select 
      replacement.id,replacement.ItemID,device.dev_detail
      from 
      replacement 
      inner join device on replacement.ItemID = device.ID
    ) as RMA
    on Rep.replaceID = RMA.id

    drop table report, device,replacement,[report-replacement]

这是所需的输出，我想帮助为主内部连接构建一个谓词连接，以便我得到报告项目与替换项目的 1 比 1 比率：

report id, item id, item details, rma_id, rma_item, rma_item_detail
1           1           det1        1        3          det3
1           2           det2        1        4          det4
2           56          det56       2        7          det7
2           34          det34       2        8          det8
2           23          det23       2        9          det9
3           15          det15       3       16          det16

这是执行示例代码的结果，问题是我得到了很多行，我只使用内部连接。不应该有那么多，我的问题是是否有特殊的连接谓词，一些聪明的方法来获得所需的输出。

report_id   reported_item   reported_item_detail    report_replace_id   rma_id  rma_item    rma_item_detail
    1           1               det1                    1               1       3           det3
    1           2               det2                    1               1       3           det3
    2           56              det56                   1               1       3           det3
    2           34              det34                   1               1       3           det3
    2           23              dev23                   1               1       3           det3
    3           15              det15                   1               1       3           det3
    1           1               det1                    1               1       4           det4
    1           2               det2                    1               1       4           det4
    2           56              det56                   1               1       4           det4
    2           34              det34                   1               1       4           det4
    2           23              dev23                   1               1       4           det4
    3           15              det15                   1               1       4           det4
    1           1               det1                    2               2       7           det7
    1           2               det2                    2               2       7           det7
    2           56              det56                   2               2       7           det7
    2           34              det34                   2               2       7           det7
    2           23              dev23                   2               2       7           det7
    3           15              det15                   2               2       7           det7
    1           1               det1                    2               2       8           det8
    1           2               det2                    2               2       8           det8
    2           56              det56                   2               2       8           det8
    2           34              det34                   2               2       8           det8
    2           23              dev23                   2               2       8           det8
    3           15              det15                   2               2       8           det8
    1           1               det1                    2               2       9           det9
    1           2               det2                    2               2       9           det9
    2           56              det56                   2               2       9           det9
    2           34              det34                   2               2       9           det9
    2           23              dev23                   2               2       9           det9
    3           15              det15                   2               2       9           det9
    1           1               det1                    3               3       16          det16
    1           2               det2                    3               3       16          det16
    2           56              det56                   3               3       16          det16
    2           34              det34                   3               3       16          det16
    2           23              dev23                   3               3       16          det16
    3           15              det15                   3               3       16          det16

它将订单的每条记录与替换的每条记录相乘，并且它应该只对先前与设备连接的两个表进行内部连接以获取详细信息，因此您可以看到每个设备号都获取其设备详细信息（16 获取16）。

我发现在加入设备详细信息表时，连接 RMA 和 Reps 都可以，但是表报告与表 [report-replacement] 的内部连接无法正常工作，因此我将 on 子句顺序从 reportID = [report-替换].reportID 到 [report-replacement].reportID = reportID，这会将行数从 36 行减少到 14 行。

标签： sqlsql-serverjoin

解决方案

这是我的问题的解决方案。

select 
rep.ID as report_id,
rep.itemID as reported_item,
rep.dev_detail as reported_item_detail,
rep.replaceID as report_replace_id,
rma.id as rma_id,
rma.ItemID as rma_item,
rma.dev_detail as rma_item_detail,
Rep.seq,
rma.seq
from
(
  select report.ID,report.itemID,device.dev_detail, [report-replacement].replaceID,
  ROW_NUMBER() OVER (PARTITION BY report.id ORDER BY report.id) AS seq
  from 
   report 
    inner join device on device.ID = Report.ID
    inner join [report-replacement] on [report-replacement].reportID = Report.ID
) as Rep
inner join
(
  select
  replacement.id,replacement.ItemID,device.dev_detail,
  ROW_NUMBER() OVER (PARTITION BY replacement.id ORDER BY replacement.id) AS seq
  from 
  replacement 
  inner join device on device.ID = replacement.ItemID
) as RMA
on rep.replaceID = RMA.id AND rep.seq=rma.seq

该问题通常称为代理交叉连接。

解决方案似乎是基于使用 ROW_NUMBER() OVER (PARTITION BY replacement.id ORDER BY replacement.id) AS seq

推荐阅读