通过匹配Server 2008中的多个表提取数据

Question

我想从多个表中提取基于匹配的结果，三个表是特定的。这些表是Despatch，Activation和Replaced.下图显示了它们中的结构和样本数据。

​

基于上述图表的预期结果是：

LotQty | ApprovedQty | DispatchDate | Installed
  15   |      10     |   2013-8-7   |    9

其结果应基于以下标准：

LotQty、ApprovedQty和Despatch表只是计数，与Despatch表不同。

Installed(Real问题)。该字段是根据计算得出的：

1. 查询应首先匹配来自SerialNo表的Despatch表中的Lot 20和SerialNo下的Lot 20在Activation表中。发现8匹配忽略了不同批量的产品1&2。
2. 接下来，对于8个匹配的序列，它应该比较日期。如果DispatchDate对于一个SerialNo小于ActivationDate，那么它被计算为1。(Product3 3的DispatchDate大于ActivationDate.。)因此，它被忽略了。对于序列的其余部分，is的DispatchDate是< ActivationDate).因此，Activation表的总结果计数为7。
3. 对于不匹配的Despatch表(Product1,2 1、2和3)中的SerialNo，它应该在Replaced表中执行匹配。它找到了所有SerialNo's的匹配，但是由于Activation，已经匹配了Product4到Product10，所以它应该只匹配3个剩余的SerialNo(Product4 1、2和3)。Product1 &2匹配在这里。接下来，它应该检查DispatchDate是否为< RecordDate.两个DispatchDate & 2都有< RecordDate，因此从Replaced表中计数为2。
4. 现在，总的Installed应该是9 (7来自Activation表，2来自Replaced表)。

在SerialNo和Activation表中都可以有来自Replaced和Activation表的多个条目，如上图所示。因此，首先，Despatch表的SerialNo的匹配应该用Activation表来完成，如果有任何SerialNo是不匹配的，或者是DispatchDate > ActivationDate，那么对于那些SerialNo表，应该继续在Replaced表中找到匹配。

到目前为止，在@ jpw的帮助下，我成功地形成了如下查询：

select
(
    select distinct LOTQty
    from Despatch
    where LotQty = '15'
)as LotQty

,(
    select COUNT(SerialNo)
    from Despatch
    where LotQty = '15'
)as ApprovedQty

,(
    select distinct(DispatchDate)
    from Despatch
    where LotQty = '15'
)as DispatchDate

,(
    select COUNT(A.SerialNo) + count(R.NewSerialNo)
    from Despatch D
    left join
        Activation A
        on D.SerialNo = A.SerialNo
        and D.DispatchDate <= A.ActivationDate
        and D.LotQty = A.LotQty
    left join
        Replaced R
        on D.SerialNo = R.NewSerialNo
        and D.DispatchDate <= R.RecordDate
)as Installed

这会产生错误的输出，例如：

LotQty | ApprovedQty | DispatchDate | Installed
  15   |      10     |   2013-8-7   |    17

我们非常感谢您的帮助，谢谢。

EIDT1

​

因此，从上面的图表到现在，Installed应该是9，因为我只想要Lot15的结果

Answer 1

问题在于您替换的数据。看看您的情况，它将匹配该表中与Dispatch表中的序列号匹配的所有记录。不过，你要指出的应该是2。您缺少一个条件，表明它在激活中无法匹配，以缩小结果。

确保A在第二个联接中的SerialNo对您有好处

select
(
    select distinct LOTQty
    from Despatch
    where LotQty = '15'
)as LotQty

,(
    select COUNT(SerialNo)
    from Despatch
    where LotQty = '15'
)as ApprovedQty

,(
    select distinct(DispatchDate)
    from Despatch
    where LotQty = '15'
)as DispatchDate

,(
    select COUNT(A.SerialNo) + count(R.NewSerialNo)
    from Despatch D
    left join
        Activation A
        on D.SerialNo = A.SerialNo
        and D.DispatchDate <= A.ActivationDate
        and D.LotQty = A.LotQty
    left join
        Replaced R
        on D.SerialNo = R.NewSerialNo
        and D.DispatchDate <= R.RecordDate
        and A.SerialNo IS NULL
    where D.LotQty = '15'
)as Installed

我现在似乎无法登录到SQLFiddle，因此，如果您想尝试一下，这里是您需要的模式。

CREATE TABLE Despatch (
    SerialNo VARCHAR(20),
    DispatchDate DATETIME,
    LOTQty INT )

CREATE TABLE Activation (
    SerialNo VARCHAR(20),
    LOTQty INT,
    ActivationDate DATETIME )

CREATE TABLE Replaced (
    NewSerialNo VARCHAR(20),
    RecordDate DATETIME )

INSERT INTO Despatch
VALUES ('Product1', '2013-08-07', 15)
INSERT INTO Despatch
VALUES ('Product2', '2013-08-07', 15)
INSERT INTO Despatch
VALUES ('Product3', '2013-08-07', 15)
INSERT INTO Despatch
VALUES ('Product4', '2013-08-07', 15)
INSERT INTO Despatch
VALUES ('Product5', '2013-08-07', 15)
INSERT INTO Despatch
VALUES ('Product6', '2013-08-07', 15)
INSERT INTO Despatch
VALUES ('Product7', '2013-08-07', 15)
INSERT INTO Despatch
VALUES ('Product8', '2013-08-07', 15)
INSERT INTO Despatch
VALUES ('Product9', '2013-08-07', 15)
INSERT INTO Despatch
VALUES ('Product10', '2013-08-07', 15)
INSERT INTO Despatch
VALUES ('Product11', '2013-08-07', 20)
INSERT INTO Despatch
VALUES ('Product12', '2013-08-07', 20)

INSERT INTO Activation
VALUES ('Product1', 55, '2013-07-13')
INSERT INTO Activation
VALUES ('Product2', 20, '2013-11-13')
INSERT INTO Activation
VALUES ('Product3', 15, '2013-07-13')
INSERT INTO Activation
VALUES ('Product4', 15, '2013-11-13')
INSERT INTO Activation
VALUES ('Product5', 15, '2013-11-13')
INSERT INTO Activation
VALUES ('Product6', 15, '2013-11-13')
INSERT INTO Activation
VALUES ('Product7', 15, '2013-11-13')
INSERT INTO Activation
VALUES ('Product8', 15, '2013-11-13')
INSERT INTO Activation
VALUES ('Product9', 15, '2013-11-13')
INSERT INTO Activation
VALUES ('Product10', 15, '2013-11-13')

INSERT INTO Replaced
VALUES ('Product1', '2013-12-07')
INSERT INTO Replaced
VALUES ('Product2', '2013-12-07')
INSERT INTO Replaced
VALUES ('Product4', '2013-12-07')
INSERT INTO Replaced
VALUES ('Product5', '2013-12-07')
INSERT INTO Replaced
VALUES ('Product6', '2013-12-07')
INSERT INTO Replaced
VALUES ('Product7', '2013-12-07')
INSERT INTO Replaced
VALUES ('Product8', '2013-12-07')
INSERT INTO Replaced
VALUES ('Product9', '2013-12-07')
INSERT INTO Replaced
VALUES ('Product10', '2013-12-07')
INSERT INTO Replaced
VALUES ('Product11', '2013-12-07')
INSERT INTO Replaced
VALUES ('Product12', '2013-12-07')