主页 / dba / 问题 / 264098
Accepted
M.I.M.F
Asked: 2020-04-02 12:35:29 +0800 CST

如何从 SQL Server 中的多个表中检索贷方借方和余额

  • 772

我有超过 1 个表，例如 INVOICE、PAYMENT、RETURN_INVOICE、RETURN_PAYMENT 等。我想检索每个客户的数据 CREDIT、DEBIT 和 BALANCE

下面是表和插入查询语句，

CREATE TABLE Customer
(
    CustomerId INT IDENTITY(1,1),
    RegistrationDate DATE,
    CustomerName VARCHAR(45),
    OpeningBalance MONEY,
    PRIMARY KEY(CustomerId)
)
INSERT INTO Customer (CustomerName, RegistrationDate, OpeningBalance) VAlUES ('JOHN', '2020-01-15', 1000)


CREATE TABLE Invoice
(
    InvoiceId INT IDENTITY(1,1),
    InvoiceDate DATE,
    CustomerId INT,
    InvoiceTotal MONEY,
    PRIMARY KEY(InvoiceId)
)
INSERT INTO Invoice (InvoiceDate, CustomerId, InvoiceTotal) VAlUES ('2020-01-16', 1, 2000)
INSERT INTO Invoice (InvoiceDate, CustomerId, InvoiceTotal) VAlUES ('2020-01-17', 1, 500)
INSERT INTO Invoice (InvoiceDate, CustomerId, InvoiceTotal) VAlUES ('2020-01-17', 1, 250)
INSERT INTO Invoice (InvoiceDate, CustomerId, InvoiceTotal) VAlUES ('2020-01-20', 1, 1000)
INSERT INTO Invoice (InvoiceDate, CustomerId, InvoiceTotal) VAlUES ('2020-01-22', 1, 2250)
INSERT INTO Invoice (InvoiceDate, CustomerId, InvoiceTotal) VAlUES ('2020-01-24', 1, 1750)
INSERT INTO Invoice (InvoiceDate, CustomerId, InvoiceTotal) VAlUES ('2020-01-28', 1, 3000)


CREATE TABLE Payment
(
    PaymentId INT IDENTITY(1,1),
    PaymentDate DATE,
    CustomerId INT,
    PaymentTotal MONEY,
    PRIMARY KEY(PaymentId)
)
INSERT INTO Payment (PaymentDate, CustomerId, PaymentTotal) VAlUES ('2020-01-18', 1, 750)
INSERT INTO Payment (PaymentDate, CustomerId, PaymentTotal) VAlUES ('2020-01-20', 1, 2000)
INSERT INTO Payment (PaymentDate, CustomerId, PaymentTotal) VAlUES ('2020-01-23', 1, 5000)
INSERT INTO Payment (PaymentDate, CustomerId, PaymentTotal) VAlUES ('2020-01-26', 1, 200)
INSERT INTO Payment (PaymentDate, CustomerId, PaymentTotal) VAlUES ('2020-01-28', 1, 500)


CREATE TABLE ReturnInvoice
(
    ReturnInvoiceId INT IDENTITY(1,1),
    ReturnInvoiceDate DATE,
    CustomerId INT,
    ReturnInvoiceTotal MONEY,
    PRIMARY KEY(ReturnInvoiceId)
)
INSERT INTO ReturnInvoice (ReturnInvoiceDate, CustomerId, ReturnInvoiceTotal) VAlUES ('2020-01-25', 1, 500)
INSERT INTO ReturnInvoice (ReturnInvoiceDate, CustomerId, ReturnInvoiceTotal) VAlUES ('2020-01-28', 1, 300)
INSERT INTO ReturnInvoice (ReturnInvoiceDate, CustomerId, ReturnInvoiceTotal) VAlUES ('2020-01-29', 1, 1000)


CREATE TABLE ReturnPayment
(
    ReturnPaymentId INT IDENTITY(1,1),
    ReturnPaymentDate DATE,
    CustomerId INT,
    ReturnPaymentTotal MONEY,
    PRIMARY KEY(ReturnPaymentId)
)
INSERT INTO ReturnPayment (ReturnPaymentDate, CustomerId, ReturnPaymentTotal) VAlUES ('2020-01-21', 1, 500)
INSERT INTO ReturnPayment (ReturnPaymentDate, CustomerId, ReturnPaymentTotal) VAlUES ('2020-01-27', 1, 2000)

我想像这样检索输出，

在此处输入图像描述

  • 所有表都应该是客户的内部连接（CustomerId 是外键）
  • 需要检索额外的列Description并提及表事务名称和No.（上图中的第3列）
  • 客户OPENING BALANCE应位于客户表的顶部行
  • 按日期排序，其下没有。

如何获得上图中的预期输出。

sql-server sql-server-2014

1 个回答

  1. Best Answer

    我相信这会做你需要的。感谢您通过提供可靠的 MVCE 使编写变得容易。

    我们在 UNION ALL 中有 5 个查询来生成我们的数据集，还有一个外部查询来生成运行总计。我们在联合中有 5 个查询的原因是我们将一个用于期初余额，另一个用于每种交易类型。

    ORDER BY Date, Description我在余额中使用的运行计算作弊了一点。因为我们在里面生成了数字Description。肯定有一种更清洁的方法可以做到这一点。如果您需要按特定类型排序，您可以在联合查询中添加一个附加列并对它们进行编码（1、2、3、4、5），然后在外部查询中按该列排序。

    但是，此查询确实可以让您到达目标线：

    SELECT
  CustomerName,
  [Date],
  Description,
  ABS(Debit) as Debit,
  ABS(Credit) as Credit,
  SUM(CASE WHEN Credit = 0 THEN DEBIT ELSE Credit END) OVER (PARTITION BY CustomerId ORDER BY [Date], Description)  as Balance
FROM

(
    SELECT
      CustomerId,
      CustomerName,
      RegistrationDate as [Date],
      'OPENING BALANCE' as Description,
      OpeningBalance as Debit,
      0 as Credit,
      OpeningBalance as Balance
    FROM Customer

UNION ALL

    SELECT
      c.CustomerId,
      CustomerName, 
      InvoiceDate as [Date], 
      'Invoice No. ' + CAST((ROW_NUMBER() OVER (PARTITION BY c.CustomerId ORDER BY c.RegistrationDate, InvoiceDate)) AS VARCHAR(10)) AS Description,
      InvoiceTotal as Debit, 
      0 as Credit, 
      OpeningBalance
    FROM Customer c
    JOIN Invoice i ON c.CustomerId = i.CustomerId

UNION ALL

    SELECT
      c.CustomerId,
      CustomerName, 
      PaymentDate,
      'Payment No. ' + CAST((ROW_NUMBER() OVER (PARTITION BY c.CustomerId ORDER BY c.RegistrationDate, PaymentDate)) AS VARCHAR(10)) AS Description,
      0, 
      -1.0 * PaymentTotal, 
      OpeningBalance
    FROM Customer c
    JOIN Payment i ON c.CustomerId = i.CustomerId

UNION ALL

    SELECT
      c.CustomerId,
      CustomerName, 
      ReturnInvoiceDate,
      'ReturnInvoice No. ' + CAST((ROW_NUMBER() OVER (PARTITION BY c.CustomerId ORDER BY c.RegistrationDate, ReturnInvoiceDate)) AS VARCHAR(10)) AS Description,
      0, 
      -1.0 * ReturnInvoiceTotal, 
      OpeningBalance
    FROM Customer c
    JOIN ReturnInvoice i ON c.CustomerId = i.CustomerId

UNION ALL

    SELECT 
      c.CustomerId,
      CustomerName, 
      ReturnPaymentDate, 
      'Return Payment No. ' + CAST((ROW_NUMBER() OVER (PARTITION BY c.CustomerId ORDER BY c.RegistrationDate, ReturnPaymentDate)) AS VARCHAR(10)) AS Description,
      ReturnPaymentTotal, 
      0, 
      OpeningBalance
    FROM Customer c
    JOIN ReturnPayment i ON c.CustomerId = i.CustomerId
) u

ORDER BY [Date]

    这是一个SQLFiddle

    • 1

相关问题