使用两个表在查询中获取和

Question

我需要TrasactionPaymentsDetails的金额

    DECLARE @mydate DATETIME
SELECT @mydate = '2012-07-28'

select sum(rd.Amount) as total,rp.TransactionCode, rp.ReferenceDate,rp.TransactionFlag,rd.Amount

 from ReceiptsPayments rp,ReceiptsPaymentsDetail rd where 
ReferenceDate >= DATEADD(dd,1-day(@mydate),@mydate)
and ReferenceDate <= DATEADD(dd,0-day(DATEADD(mm,1,@mydate)),DATEADD(mm,1,@mydate))
and TransactionFlag='P';

以下是错误。请帮帮忙

Msg 8120、级别16、状态1、第6行列'ReceiptsPaymentsDetail.TransactionCode‘在select列表中无效，因为它既不包含在聚合函数中，也不包含在GROUP子句中。

Answer 1

试试这个-

DECLARE @mydate DATETIME
SELECT @mydate = '20120728'

SELECT  
      total = SUM(rd.Amount) 
    , rp.TransactionCode
    , rp.ReferenceDate
    , rp.TransactionFlag
FROM dbo.ReceiptsPayments rp
JOIN dbo.ReceiptsPaymentsDetail rd ON ON rp.ID = rd.ReceiptPaymentsID
WHERE ReferenceDate >= DATEADD(dd, 1 - DAY(@mydate), @mydate)
    AND ReferenceDate <= DATEADD(dd, - DAY(DATEADD(mm, 1, @mydate)), DATEADD(mm, 1, @mydate))
    AND TransactionFlag = 'P'
GROUP BY 
      rp.TransactionCode
    , rp.ReferenceDate
    , rp.TransactionFlag

Answer 2

您需要GROUP BY：

DECLARE @mydate DATETIME

SELECT @mydate = '2012-07-28'

SELECT Sum(rd.amount) AS total,
       rp.transactioncode,
       rp.referencedate,
       rp.transactionflag
FROM   receiptspayments rp,
       INNER JOIN receiptspaymentsdetail rd ON rp.id=rd.receiptpaymentsid
WHERE  referencedate >= Dateadd(dd, 1 - Day(@mydate), @mydate)
       AND referencedate <= Dateadd(dd, 0 - Day(Dateadd(mm, 1, @mydate)),
                            Dateadd(mm, 1, @mydate))
       AND transactionflag = 'P'
GROUP  BY rp.transactioncode,
          rp.referencedate,
          rp.transactionflag;  

此外，您还需要链接您的表，以避免生成CROSS JOIN，我认为这不是您想要的。我以为在您的Id表中有一个receiptpayments，receiptpaymentsdetails中有一个FK，因此出现了INNER JOIN。

Answer 3

在最后一个Group By条件之后将where子句添加到语句中：

Group By rp.TransactionCode, rp.ReferenceDate,rp.TransactionFlag