在SQL Server 2008中匹配三个表 [英] Matching three tables in SQL Server 2008

问题描述

我有三个表发货，激活和替换，如下所示：

发货表：

ProductNo    VendorName  DispatchDate    LotQty

产品1       供应商1          2013年7月8日          20

Product2       供应商2          2013年7月8日          20

产品3       供应商3          2013年7月8日          20

Product4       供应商4          2013年7月8日          20

Product5       供应商5          2013年7月8日          20

Product6       供应商6          2013年7月8日          20



激活表：

ProductNo    LotQty    ActivationDate

Product11      55          13/11/2013

产品12      55          13/11/2013

Product3        55          13/11/2013

Product4        55          13/11/2013

Product5        55          13/11/2013

Product6        55          13/11/2013



替换表

ProductNo    RecordDate

Product1        2013年7月12日

产品7        2013年7月12日

Product8        2013年7月12日

产品9        2013年7月12日

产品10        2013年7月12日

产品11        2013年7月12日



我想比较所有三个表中的数据，其中Despatch表为基数。



例如，如果我输入LOTQty为20，我将根据以下计算得到结果：



1.>它应匹配Desopatch表中Lot 20下的所有ProductNo和Activation表中的ProductNo。如果找到匹配（在这种情况下为Product3,4,5& 6），那么接下来它应该检查日期。



2.>如果匹配的ProductNo的发货日期小于激活日期，那么它应该考虑。（产品3的发货日期小于激活日期。因此我们将其计为1，对所有其他日期类似）。因此我们找到4场比赛。



3.> Product1和Product2在激活表中未找到任何匹配项。对于此类产品编号，请参阅替换表。如果找到匹配（在本例中为Product1），则应比较Dispatch Date和Record Date。如果发货日期只比记录日期小，那么我们应该考虑数据（Product1的发货日期小于RecordDate。所以我们算作1）。



所以现在通过比较激活表和1，通过与替换表进行比较，总数量应为5即4。



以上只是示例表条目。我的真实表包含更多列，这些列经过修剪以保持简单。我尝试准备查询，但我无法找到符合我自己要求的逻辑。我到目前为止尝试的查询如下：

 选择 
 d.LotQty，
 ApprovedQty = count（d.ProductNo），
 d.DispatchDate，
已安装= count（a.ProductNo）+ count（r.ProductNo）
 来自 
发货d 
 左 加入 
激活
   d.ProductNo = a.ProductNo 
 和 d.DispatchDate< a.ActivationDate 
 和 d.LotQty = a.LotQty 
  left   join  
替换r 
   d.ProductNo = r.ProductNo 
 < span class =code-keyword>和 d.DispatchDate< r.RecordDate 
 其中 
 d.LotQty =  20  
 < span class =code-keyword> group   by  
 d.LotQty，d.DispatchDate 

结果如下：

LotQty      ApprovedQty      DispatchDate     已安装

20                6                          2013-8-7            5



但是当我尝试使用我的实际表时，它给出了两列的意外结果，即ApprovedQty和Installed。我批准的数量和批量'1007'的安装量应分别为984和869。但是查询返回了990和1633，大约是预期的已安装数据的两倍。



将 Despatch 表的ProductNo与激活和替换进行比较时，可能会有激活和替换表中可能存在相同ProductNo条目的可能性。因此，查询应首先使用激活表进行检查。如果在那里找到ProductNo的匹配项，则不应在替换表中搜索相同的ProductNo。只有那些在激活表中找不到匹配或其DispatchDate大于ActivationDate的ProductNo才能与替换表匹配。



帮助非常感谢。在此先感谢

解决方案

我使用了递归 CTE [ ^ ] （公用表表达式）在以下sql中 - 参见CodeProject文章如何在T-SQL中使用递归CTE调用 [ ^ ]



我的想法如下......过滤掉我需要的东西首先从激活表中删除（暂时忽略LotQty）...

 使用 T1（ProductNo， DispatchDate，LotQty）
  AS  
（
  SELECT  D.ProductNo ，D.DispatchDate，D.LotQ ty 
 来自 Despatch D 
  INNER   JOIN 激活A  ON  D.ProductNo = A.ProductNo  AND  D.DispatchDate< A.ActivationDate 
）

然后用替换表

可能想做同样的事情> 使用 T2（ProductNo，DispatchDate，LotQty）
  AS  
（
  SELECT  D.ProductNo，D.DispatchDate，D.LotQty 
  FROM  Despatch D 
  INNER   JOIN 替换R  ON  D.ProductNo = R.ProductNo  AND  D.DispatchDate< R.RecordDate 
）

我想从T1获得一切只有来自T2的东西，我还没有把它放进T3。我添加了[Source]列只是为了显示数据的真正来源

 T3（ Source  ，ProductNo，DispatchDate，LotQty）
  AS  
（
  SELECT  '  T1'，T1。*  FROM  T1 
  UNION   SELECT  '  T2'，*  FROM  T2 其中 T2 .ProductNo  NOT   IN （ SELECT  T1。 ProductNo 来自 T1）
）

对结果的快速查询显示产品3,4,5,6正在获取数据从T1开始，Product1从T2获取，而Product2没有其他数据。



最后使用 Despatch 作为基础进行分组/总计等，这样整个事情就变成了

 使用 T1（ProductNo，DispatchDate，LotQty）
  AS  
（
  SELECT  D.ProductNo，D.DispatchDate，D.LotQty 
 来自发送D 
  INNER   JOIN 激活A  ON  D.ProductNo = A.ProductNo  AND  D.DispatchDate< A.ActivationDate 
），
 T2（ProductNo，DispatchDate， LotQty）
  AS  
（
  SELECT  D.ProductNo，D .DispatchDate，D.LotQty 
  FROM 发​​货D 
  INNER   JOIN 替换ced R  ON  D.ProductNo = R.ProductNo  AND  D.DispatchDate< R.RecordDate 
），
 T3（ Source ，ProductNo，DispatchDate，LotQty）
  AS  
（
  SELECT  '  T1'，T1。*  FROM  T1 
  UNION   SELECT  '  T2'，*  FROM  T2 其中 T2.ProductNo  NOT   IN （ SELECT  T1.ProductNo 来自 T1）
）
  SELECT  
 D.LotQty，
 ApprovedQty = count（D.ProductNo），
 D.DispatchDate ，
已安装=计数（T3.ProductNo）
  FROM 发​​货D 
  LEFT   OUTER   JOIN  T3  ON  T3.ProductNo = D.ProductNo 
  WHERE  D.LotQty =  20  
  GROUP   BY  D.LotQty，D.DispatchDate 

I have three tables Despatch, Activation and Replaced as below:
Despatch table:
ProductNo     VendorName   DispatchDate     LotQty
Product1         Vendor1           7/8/2013           20
Product2         Vendor2           7/8/2013           20
Product3         Vendor3           7/8/2013           20
Product4         Vendor4           7/8/2013           20
Product5         Vendor5           7/8/2013           20
Product6         Vendor6           7/8/2013           20

Activation table:
ProductNo     LotQty     ActivationDate
Product11       55           13/11/2013
Product12       55           13/11/2013
Product3         55           13/11/2013
Product4         55           13/11/2013
Product5         55           13/11/2013
Product6         55           13/11/2013

Replaced table
ProductNo     RecordDate
Product1         7/12/2013
Product7         7/12/2013
Product8         7/12/2013
Product9         7/12/2013
Product10         7/12/2013
Product11         7/12/2013

I want to compare data from all the three tables with Despatch table being base.

For example, if I enter LOTQty as 20 I should receive result based on following calculations:

1.> It should match all the ProductNo under Lot 20 from Despatch table with ProductNo in Activation table. If match found(in this case Product3,4,5 & 6) then next it should check dates.

2.> If Dispatch Date of matched ProductNo's are less than Activation Date only then it should consider.(Product3's Dispatch Date is less than Activation Date. So we count it as 1 and similarly for all others). Hence we find 4 matches.

3.> Product1 and Product2 did not found any match in Activation table. For such Product numbers we refer Replaced table. If match found (in this case Product1) it should compare Dispatch Date and Record Date. If Dispatch Date is lesser than Record Date only then we should consider the data(Product1's Dispatch date is less than RecordDate. So we count it as 1).

So now total quantity should be 5 i.e 4 by comparing Activation table and 1 by comparing with Replaced table.

The above are just sample table entries. My real tables contains many more columns which are trimmed to keep it simple. I tried preparing the query but I'm unable to find logic to cater my own requirement. Query I tried so far is as below:

select 
    d.LotQty, 
    ApprovedQty = count(d.ProductNo),
    d.DispatchDate,
    Installed = count(a.ProductNo) + count(r.ProductNo)
from 
    Despatch d 
left join 
    Activation a 
     on d.ProductNo = a.ProductNo 
    and d.DispatchDate < a.ActivationDate 
    and d.LotQty = a.LotQty
left join 
    Replaced r 
      on d.ProductNo = r.ProductNo 
     and d.DispatchDate < r.RecordDate
where 
    d.LotQty = 20
group by 
    d.LotQty, d.DispatchDate

This yields result as below:
LotQty       ApprovedQty       DispatchDate       Installed
20                 6                           2013-8-7             5

But when I try for my actual table it gives me unexpected result for two columns i.e "ApprovedQty" and "Installed". My approved Qty and Installed for Lot '1007' should be 984 and 869 respectively. but query returned me 990 and 1633, approx twice the expected Installed data.

While comparing Despatch table's ProductNo with Activation and Replaced, there may be possibility that there might be entries for same ProductNo in both Activation and Replaced tables. Hence the query should first check with Activation table. If it finds a match for ProductNo there, it shuould not search for same ProductNo in Replaced table. Only those ProductNo which found no match in Activation table or whose DispatchDate is greater than ActivationDate should be matched with Replaced table.

Help is deeply appreciated. Thanks in advance

解决方案

I've used recursive CTEs[^] (Common Table Expressions) in the following sql - see CodeProject Article How to use recursive CTE calls in T-SQL[^]

My thinking went as follows ... filter out what I need from the Activation table first (ignoring the LotQty for now) ...

With T1(ProductNo, DispatchDate, LotQty)
AS
(
    SELECT D.ProductNo, D.DispatchDate, D.LotQty
    from Despatch D
    INNER JOIN Activation A ON  D.ProductNo = A.ProductNo AND D.DispatchDate < A.ActivationDate
)

Then do the same with what I might want from the Replaced Table

With T2(ProductNo, DispatchDate, LotQty)
AS
(
    SELECT D.ProductNo, D.DispatchDate, D.LotQty
    FROM Despatch D
    INNER JOIN Replaced R ON D.ProductNo = R.ProductNo AND D.DispatchDate < R.RecordDate
)

I want to get everything from T1 and only the stuff from T2 that I don't already have and put it into T3. I added the [Source] column just to show where the data was really coming from

T3(Source, ProductNo, DispatchDate, LotQty)
AS
(
    SELECT 'T1', T1.* FROM T1
    UNION SELECT 'T2', * FROM T2 where T2.ProductNo NOT IN (SELECT T1.ProductNo from T1)
)

A quick query of the results shows that Products 3,4,5,6 are getting the data from T1, Product1 is getting it from T2 and Product2 has no other data.

Finally do the grouping/totals etc using Despatch as the base so the whole thing together becomes

With T1(ProductNo, DispatchDate, LotQty)
AS
(
    SELECT D.ProductNo, D.DispatchDate, D.LotQty
    from Despatch D
    INNER JOIN Activation A ON  D.ProductNo = A.ProductNo AND D.DispatchDate < A.ActivationDate
),
T2(ProductNo, DispatchDate, LotQty)
AS
(
    SELECT D.ProductNo, D.DispatchDate, D.LotQty
    FROM Despatch D
    INNER JOIN Replaced R ON D.ProductNo = R.ProductNo AND D.DispatchDate < R.RecordDate
),
T3(Source, ProductNo, DispatchDate, LotQty)
AS
(
    SELECT 'T1', T1.* FROM T1
    UNION SELECT 'T2', * FROM T2 where T2.ProductNo NOT IN (SELECT T1.ProductNo from T1)
)
SELECT 
   D.LotQty,
   ApprovedQty = count(D.ProductNo),
   D.DispatchDate,
   Installed = count(T3.ProductNo)
FROM Despatch D
LEFT OUTER JOIN T3 ON T3.ProductNo = D.ProductNo
WHERE D.LotQty = 20
GROUP BY D.LotQty, D.DispatchDate

这篇关于在SQL Server 2008中匹配三个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！