主页 / dba / 问题 / 123004
Accepted
M.I.M.F
Asked: 2015-12-06 21:02:17 +0800 CST

使用三表连接查找超额支付金额

  • 772

如何使用内部联接从以下 3 个表中仅查找 1 号客户的超额支付值？明智的每个客户我都需要检索查询。

例如1：

一旦我只检索到客户 2，答案应该是 0，因为他开具的发票总额为 300，而他为发票支付的总额仅为 100，因此多付款项为 0。

例如2：

一旦我只检索到客户 1，答案应该是 500，因为他开具的发票总额为 1500，而他为发票支付的总额仅为 2000，因此多付了 500。

在此处输入图像描述

sql-server

1 个回答

  1. Best Answer

    没有直接的方法，只需链接这些表即可给出结果。

    下面的代码给了你答案，还有一个好处是，让@ColId 为空，每个 Client 都会被处理。

    你可能会想：这在计算上是昂贵的！

    是的，看起来可能是这样。但是，请检查代码片段后的补充注释。

    DECLARE @CliNo INT

SET @CliNo = 1

;

WITH pay AS
(
    SELECT cli.[Client No], (pay.Amount - inv.Amount) AS DeltaPayment
    FROM
        dbo.[Client TBL] cli
            INNER JOIN(
                SELECT inv.[Client No], SUM(inv.Amount) AS Amount
                FROM dbo.[Invoice TBL] inv
                GROUP BY inv.[Client No]
            ) inv
            ON inv.[Client No] = cli.[Client No]
            --
            INNER JOIN(
                SELECT pay.[Client No], SUM(pay.Amount) AS Amount
                FROM dbo.[Payment TBL] pay
                GROUP BY pay.[Client No]
            ) pay
            ON pay.[Client No] = cli.[Client No]
    WHERE cli.[Client No] = @CliNo OR @CliNo IS NULL
)
SELECT
    pay.[Client No],
    CASE WHEN pay.DeltaPayment > 0 THEN pay.DeltaPayment
                                   ELSE 0
    END AS OverPayment
FROM pay

    乍一看，我们似乎是在要求 SQL Server 处理SUM每张发票、SUM每笔付款，然后才计算任何超额付款。

    但是，SQL Server 2012 是一个非常聪明的野兽。

    查看实际的执行计划：

    在此处输入图像描述

    SQL Server 查询引擎在汇总之前过滤付款和发票记录。

    因此，没有性能损失，尽管这不是您所要求的 - 一个简单的查询与树直接 INNER JOINS。

    此 POC 的完整代码如下：

    CREATE TABLE [Client TBL]([Client No] INT NOT NULL PRIMARY KEY)
CREATE TABLE [Invoice TBL]([Invoice No] INT NOT NULL PRIMARY KEY, Amount MONEY, [Client No] INT NOT NULL FOREIGN KEY ([Client No]) REFERENCES [Client TBL])
CREATE TABLE [Payment TBL]([Payment No] INT NOT NULL PRIMARY KEY, Amount MONEY, [Client No] INT NOT NULL FOREIGN KEY ([Client No]) REFERENCES [Client TBL])
go

INSERT INTO dbo.[Client TBL] VALUES(1), (2)
INSERT INTO dbo.[Invoice TBL] VALUES(1, 200, 1), (2, 100, 2), (3, 500, 1), (5, 300, 1), (6, 200, 2), (7, 500, 1)
INSERT INTO dbo.[Payment TBL] VALUES(1, 1000, 1), (2, 100, 2), (4, 1000, 1)
GO

DECLARE @CliNo INT

SET @CliNo = 1

;

WITH pay AS
(
    SELECT cli.[Client No], (pay.Amount - inv.Amount) AS DeltaPayment
    FROM
        dbo.[Client TBL] cli
            INNER JOIN(
                SELECT inv.[Client No], SUM(inv.Amount) AS Amount
                FROM dbo.[Invoice TBL] inv
                GROUP BY inv.[Client No]
            ) inv
            ON inv.[Client No] = cli.[Client No]
            --
            INNER JOIN(
                SELECT pay.[Client No], SUM(pay.Amount) AS Amount
                FROM dbo.[Payment TBL] pay
                GROUP BY pay.[Client No]
            ) pay
            ON pay.[Client No] = cli.[Client No]
    WHERE cli.[Client No] = @CliNo
)
SELECT
    pay.[Client No],
    CASE WHEN pay.DeltaPayment > 0 THEN pay.DeltaPayment
                                   ELSE 0
    END AS OverPayment
FROM pay
go
    • 2

相关问题