主页 / dba / 问题 / 101628
Accepted
M.I.M.F
Asked: 2015-05-16 06:45:07 +0800 CST

如何在SQL中获取两个表值的一条语句

  • 772

我想在一个语句中检索两个表值，例如，（示例银行报表..）

表 1：发票

SELECT ClientNo, InvoiceDate, InvoiceNo, TotalDue 
FROM Invoice_TBL 
WHERE ClientNo = 1 
ORDER BY InvoiceDate ASC

在此处输入图像描述

表 2：付款

SELECT ClientNo, PaymentDate, TotalAmountPaid 
FROM Payment_TBL 
WHERE ClientNo = 1 
ORDER BY PaymentDate ASC

在此处输入图像描述

问题是我如何合并两个表值并从客户端明智的日期升序检索数据

我想要像下面截图这样的结果

在此处输入图像描述

我是 SQL 数据库的新手。谢谢

sql-server-2008-r2

1 个回答

  1. Best Answer

    这不完全是想要的输出，但非常接近：

    查询版本 2012+：

    ; WITH invoices_payments AS
  ( SELECT StatementDate = InvoiceDate, InvoiceNo, TotalDue, TotalPaid = 0.0,
           ID = InvoiceID, Type = 0
    FROM Invoice_TBL 
    WHERE ClientNo = 1 

    UNION ALL

    SELECT PaymentDate, '', 0.0, TotalAmountPaid, 
           ID = PaymentID, Type = 1
    FROM Payment_TBL 
    WHERE ClientNo = 1 
  )
SELECT StatementDate, InvoiceNo, TotalDue, TotalPaid,
       TotalBalance = SUM(TotalDue - TotalPaid) 
                        OVER (ORDER BY StatementDate, Type, ID
                              ROWS UNBOUNDED PRECEDING)
FROM invoices_payments
ORDER BY StatementDate, Type, ID ;

    在SQLfiddle测试。我假设你在表中有一个主键（使用InvoiceID和PaymentID），我用它来 100% 确定订购（所以如果你在同一天有多个发票或付款，你总是得到相同的订单在结果中。）

    对于 2012 年之前的版本，有多种方法，没有一种方法像OVER (ORDER BY ... ROWS ...)2012 年那样有效。您可以查看 Aaron Bertrand 撰写的关于各种方法的博客文章： Best approaches for running totals – updated for SQL Server 2012。更有效的（对于 2008R2 版本和更早的版本）是 - 正如博客文章测试所示 -游标和CLR方法。

    以下是使用自连接的一种方式：

    ; WITH invoices_payments AS
  ( SELECT StatementDate = InvoiceDate, InvoiceNo, TotalDue, TotalPaid = 0.0,
           ID = InvoiceID, Type = 0
    FROM Invoice_TBL 
    WHERE ClientNo = 1 

    UNION ALL

    SELECT PaymentDate, '', 0.0, TotalAmountPaid, 
           ID = PaymentID, Type = 1
    FROM Payment_TBL 
    WHERE ClientNo = 1 
  )
SELECT a.StatementDate, a.InvoiceNo, a.TotalDue, a.TotalPaid,
       TotalBalance = SUM(b.TotalDue - b.TotalPaid) 
FROM invoices_payments AS a
  JOIN invoices_payments AS b
    ON b.StatementDate < a.StatementDate
    OR b.StatementDate = a.StatementDate AND b.Type < a.Type
    OR b.StatementDate = a.StatementDate AND b.Type = a.Type AND b.ID <= a.ID
GROUP BY a.StatementDate, a.Type, a.ID, a.InvoiceNo, a.TotalDue, a.TotalPaid
ORDER BY a.StatementDate, a.Type, a.ID ;
    • 3

相关问题