如何识别已连接的表行已被更改？

Question

我有以下简化表结构：

CREATE TABLE CustTrans
(
  CUSTACCOUNT VARCHAR(20),
  JOURNALID   BIGINT,
  VALUE       NUMERIC(28,12)
)
CREATE TABLE CustJour
(
  ID BIGINT
)

和下面的查询(也是简化的)：

DECLARE @LastJourId BIGINT

SELECT ct.CUSTACCOUNT ,
       SUM(ct.VALUE + CASE WHEN @LastJourId != ct.JOURNALID THEN dbo.fnGetAdditionalCharges(ct.JOURNALID) ELSE 0 END)),
       @LastJourId = ct.JOURNALID
FROM CustTrans      AS ct
INNER JOIN CustJour AS cj ON cj.ID = ct.JOURNALID
GROUP BY ct.CUSTACCOUNT
ORDER BY ct.CUSTACCOUNT

这将给出错误消息：

A SELECT statement that assigns a value to a variable must not be combined with
data-retrieval operations.

函数fnGetAdditionalCharges计算应用于日记帐的额外费用。因此，它应该在每个日志中运行一次。

有没有办法在一次查询中计算出日记账的交易金额和附加费用？

在上面的例子中，我试图用局部变量来解决这个问题，给它赋值最后一个日志值，这就是我试图知道日志行是否被更改的方法。

有很多类似的查询。而更好的解决方案是对查询做一些小的修改。我知道使用两个不同分组的SELECT语句的解决方案-第一个是通过CUSTACCOUNT和JOURNALID)，第二个是通过CUSTACCOUNT并在该步骤中添加fnGetAdditionalCharges值。

示例数据：

INSERT INTO CustJour ( ID ) VALUES (1);
INSERT INTO CustTrans ( CUSTACCOUNT, JOURNALID, VALUE ) VALUES ('1', 1, 1), ('1', 1, 1);

为简化起见，假设函数fnGetAdditionalCharges每次返回1。在SELECT语句中，您可以随意将函数调用替换为1

在这种情况下，预期的结果将是3.2-对于CustTran.VALUE的总和，另外1作为现有日记帐的费用。

可以有很多日志。每个日记帐可能包含许多事务。

Answer 1

将查询分解成更小的部分并没有错；优化器一直都在这样做，那么为什么我们不能呢？

如果我没理解错的话，你需要这样的东西：

with x as (
    SELECT 
        ct.CUSTACCOUNT, ct.JOURNALID,
        SUM(ct.VALUE) AS Value
    FROM 
        CustTrans AS ct
    INNER JOIN 
        CustJour AS cj ON cj.ID = ct.JOURNALID
    GROUP BY ct.CUSTACCOUNT, ct.JOURNALID
)
select CustAccount, sum(Value + dbo.fnGetAdditionalCharges(x.JOURNALID))
from x
GROUP BY 
    x.CUSTACCOUNT
ORDER BY 
    x.CUSTACCOUNT

当然是未经测试的。就像其他人说的，请给我们一些样本数据来验证它。

Answer 2

您的问题是，SELECT语句既可以创建结果集，也可以为变量赋值，但不能同时创建这两个结果集。我建议将其分成多个部分；首先将CUSTACCOUNT、VALUE和JOURNALID选择到一个临时表中，然后根据临时表中JOURNALID的值计算@LastJourId，然后从临时表中结合@LastJourId重新选择以获得最终结果集。

Answer 3

SELECT  CUSTACCOUNT, SUM(Value) + ISNULL(dbo.fnGetAdditionalCharges(journalid), 0) as Custaccvalue
FROM 
(    
     SELECT  distinct ct.CUSTACCOUNT, SUM(ct.VALUE) as Value, ct.JOURNALID as journalid
     FROM 
         CustTrans AS ct
     INNER JOIN 
              CustJour AS cj ON cj.ID = ct.JOURNALID
     GROUP BY 
             ct.CUSTACCOUNT, ct.JOURNALID
    ) a
GROUP BY 
    CUSTACCOUNT    
ORDER BY 
    CUSTACCOUNT

演示：http://sqlfiddle.com/#!3/d910d/14