SQL Server : row present in one query, 在另一个中丢失

Question

好吧，我想我一定是对 SQL 查询有什么误解。这是一个非常冗长的问题，所以感谢您花时间阅读它(我的问题就在最后，其他一切都只是上下文)。

我正在编写一个基于复式记账本金的会计系统 - 资金总是在帐户之间移动，一笔交易是 2 个或更多 TransactionParts 行，减少一个帐户并增加另一个帐户。

某些 TransactionParts 行可能会被标记为与税务相关，以便系统可以生成总增值税销售/购买等的报告，因此单个交易可能有两个 TransactionParts 引用同一账户——一个与增值税相关，另一个则不相关。为了简化向用户的演示，我有一个 View 可以合并同一帐户和交易的多行:

create view Accounting.CondensedEntryView as
select p.[Transaction], p.Account, sum(p.Amount) as Amount
    from Accounting.TransactionParts p 
    group by p.[Transaction], p.Account

然后我有一个计算运行余额列的 View ，如下所示:

create view Accounting.TransactionBalanceView as
with cte as
(
    select ROW_NUMBER() over (order by t.[Date]) AS RowNumber, 
                         t.ID as [Transaction], p.Amount, p.Account
        from Accounting.Transactions t
            inner join Accounting.CondensedEntryView p on p.[Transaction]=t.ID
)
select b.RowNumber, b.[Transaction], a.Account, 
                coalesce(sum(a.Amount), 0) as Balance
    from cte a, cte b
    where a.RowNumber <= b.RowNumber AND a.Account=b.Account
    group by b.RowNumber, b.[Transaction], a.Account

由于我尚未弄清楚的原因，某笔交易 (ID=30) 没有出现在用户的帐户对账单上。我通过运行确认了这一点

select * from Accounting.TransactionBalanceView where [Transaction]=30

这给了我以下结果:

RowNumber            Transaction Account Balance
-------------------- ----------- ------- ---------------------
72                   30          23      143.80

正如我之前所说，每个事务至少应该有两个 TransactionParts，因此我认为其中一个没有出现。我认为我编写 View 的方式一定有问题，并运行查询以查看是否还缺少其他内容:

select [Transaction], count(*) 
    from Accounting.TransactionBalanceView 
    group by [Transaction] 
    having count(*) < 2

此查询不返回任何结果——即使对于事务 30 也不返回!我想我一定是个白痴，所以运行了以下查询:

select [Transaction] 
    from Accounting.TransactionBalanceView 
    where [Transaction]=30

它返回两行!因此 select * 仅返回一行，而 select [Transaction] 返回两行。经过一番绞尽脑汁并重新运行最后两个查询后，我得出的结论是我根本不知道发生了什么。有什么想法吗？

非常感谢您到目前为止一直支持我!

编辑:

以下是执行计划:

select *
select [Transaction]

每行 1000 行，因此需要寻找其他地方来托管。

编辑2:

为了完整起见，以下是我使用的表格:

create table Accounting.Accounts
(
ID              smallint        identity primary key,
[Name]          varchar(50)     not null
    constraint UQ_AccountName unique,
[Type]          tinyint         not null
    constraint FK_AccountType foreign key references Accounting.AccountTypes
);

create table Accounting.Transactions
(
ID              int             identity primary key,
[Date]          date            not null default getdate(),
[Description]   varchar(50)     not null,
Reference       varchar(20)     not null default '',
Memo            varchar(1000)   not null
);

create table Accounting.TransactionParts
(
ID              int             identity primary key,
[Transaction]   int             not null
    constraint FK_TransactionPart foreign key references Accounting.Transactions,
Account         smallint        not null
    constraint FK_TransactionAccount foreign key references Accounting.Accounts,
Amount          money           not null,
VatRelated      bit             not null default 0
);

Answer 1

演示可能的解释。

创建表脚本

SELECT *
INTO #T
 FROM master.dbo.spt_values

 CREATE NONCLUSTERED INDEX [IX_T] ON  #T ([name] DESC,[number] DESC);

查询一(返回 35 个结果)

WITH cte AS
(
SELECT *, ROW_NUMBER() OVER (ORDER BY NAME) AS rn
 FROM  #T
)
SELECT c1.number,c1.[type] 
FROM cte c1
JOIN cte c2 ON c1.rn=c2.rn AND c1.number <> c2.number

查询二(与之前相同，但将 c2.[type] 添加到选择列表使其返回 0 个结果);

WITH cte AS
(
SELECT *, ROW_NUMBER() OVER (ORDER BY NAME) AS rn
 FROM  #T
)
SELECT c1.number,c1.[type] ,c2.[type] 
FROM cte c1
JOIN cte c2 ON c1.rn=c2.rn AND c1.number <> c2.number

为什么？

未指定重复名称的 row_number()，因此它只是选择最适合所需输出列的最佳执行计划的一个。在第二个查询中，这对于两个 cte 调用都是相同的，在第一个查询中，它选择不同的访问路径，并产生不同的 row_numbering。

建议的解决方案

您将在 ROW_NUMBER() 上自行加入 CTE(订购日期为 t.[Date])

与预期相反，CTE 可能 not be materialised这将确保自连接的一致性，因此您假设两侧的 ROW_NUMBER() 之间存在相关性，而对于存在重复 [Date] 的记录来说，这种相关性很可能不存在数据中。

如果您尝试 ROW_NUMBER() over (order by t.[Date], t.[id]) 来确保在出现捆绑日期时，row_numbering 的顺序保证一致，会怎么样？ 。 (或者其他一些列/列的组合，如果 id 不能区分记录)