当我尝试运行以下查询时
seq = row_number() over
(
partition by t.CustID
order by t.InvoiceID,
t.Date,
CASE WHEN t.S_Type = 'Receipt Voucher' THEN 1 ELSE 2 END
)
;
WITH cte
AS (
SELECT CustID,
[InvoiceID],
S_Type,
DATE,
Debit,
Credit,
seq = row_number() OVER (
PARTITION BY CustID,
ORDER BY InvoiceID,
DATE,
CASE
WHEN S_Type = 'Receipt Voucher'
THEN 1
ELSE 2
END
)
FROM Statement
)
SELECT c.[InvoiceID],
c.S_Type AS Type,
c.DATE,
.Debit,
c.Credit,
b.Balance
FROM cte c
CROSS APPLY (
SELECT Balance = SUM(Debit) - SUM(Credit)
FROM cte AS x
WHERE x.CustID = c.CustID
AND x.seq <= c.seq
) b
WHERE c.CustID = '48'
AND DATE BETWEEN '2015-01-01'
AND '2016-01-01'
ORDER BY seq
我得到以下错误:
消息102,级别15,状态1,第1行"="附近的语法不正确。消息156,级别15,状态1,第21行关键字"ORDER"附近的语法不正确。
问题就在您的Cte
:中
...
seq = row_number() OVER (
PARTITION BY CustID,
ORDER BY InvoiceID,
DATE,
CASE
WHEN S_Type = 'Receipt Voucher'
THEN 1
ELSE 2
END
)
...
在PARTITION BY CustID
之后有一个,
,正确的语法是没有逗号:
...
seq = row_number() OVER (
PARTITION BY CustID
ORDER BY InvoiceID,
DATE,
CASE
WHEN S_Type = 'Receipt Voucher'
THEN 1
ELSE 2
END
)
...
查询中的另一个问题是,在最终的SELECT
:中没有Debit
字段的别名
SELECT c.[InvoiceID],
c.S_Type AS Type,
c.DATE,
.Debit, <-- Here
c.Credit,
b.Balance
完整的查询如下:
;With Cte As
(
Select CustId, InvoiceId, S_Type, Date, Debit, Credit,
Seq = Row_Number() Over ( Partition By CustId
Order By InvoiceId,
Date,
Case
When S_Type = 'Receipt Voucher'
Then 1
Else 2
End
)
From Statement
)
Select C.InvoiceID,
C.S_Type As Type,
C.Date,
C.Debit,
C.Credit,
B.Balance
From Cte C
Cross Apply
(
Select Balance = Sum(X.Debit) - Sum(X.Credit)
From Cte X
Where X.CustId = C.CustId
And X.seq <= C.seq
) B
Where C.CustID = '48'
And C.Date Between '2015-01-01' And '2016-01-01'
Order By C.seq