|
|
我在尝试SQL Server在2008年的视图中获取运行总数
( m" m9 C, l. p% n( f这是我的表/ @- m0 G/ B2 K# K: a" |
BankAccounts------------AccountID (KEY)NameCreatedTransactions------------TransactionID (KEY)DescriptionCreditDebitTransDateCreatedAccountID ```到目前为止，这是我的查询。SELECT t.Created,t.Description,t.Credit,t.Debit,t.TransDate,t.TransactionID,ba.AccountID,9 \) {% E- i, c, @1 `
(isnull(t.Credit,0)-isnull(t.Debit,0)) COALESCE((SELECT SUM(isnull(Credit,0)) - SUM(isnull(Debit,0))4 u" a+ z9 t$ I3 ]$ C$ p; d3 z
FROM Transactions b " c$ a( V g9 N) V7 r
WHERE b.TransDate
0 g& V$ h- Y, ~! }9 h: @我得到的是..TransDate Credit Debit RunningTotal& F; z4 Z' Z/ g3 x
2011-10-08 20:14:00NULL 49494949 .25
M) g! C& u8 i7 R% J2 L2011-10-08 20:14:00 .111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111NULL .36
6 ^& m' z. q- b1 v6 M, U2011-10- 420:14:00.255 NULL .25- y$ V* V0 n( T! H3 Z+ h, b) y
2011-10-20:14:00NULL .255 191919# v) G i+ Y$ T6 K3 C! j4 w" x i
2011-10-05 20:14:00 3100.255 NULL .25
5 }1 d5 p* o1 `! B+ ~: X9 o$ ]它应该是什么样的...TransDate Credit Debit Running Total
+ s" L' \7 c8 J2011-10-08 00:31:32.957 NULL 5151515151 .36" W z8 w+ A% M# J5 j
2011-10-08 00:31:32.957 2.111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111NULL .363 }% u" z( F# L; ? | V/ G! A( o" d4 v
2011-10-07 00:31:32.957 42.255 NULL .25
6 k) H+ n; w N$ h' B! E2011-10-06 00:31:32.957 NULL .255 191919: n* }( j$ p1 k! k$ X7 y' z% t
2011-10-05 00:31:32.960 31.255 NULL 31.25
. @1 `6 I- L. j9 ?( J" R2 g```! h5 W4 V+ h2 m' m- x9 u
我真的很亲近..当天似乎有两笔交易，计算不正确..有什么想法吗？# Q3 G) q H& W6 M! b, K
; l8 ~4 F j1 |0 R! ~" H) ^ 解决方案: ! ]; O: O3 y( [9 i2 N8 v
我使用ROW_NUMBERAND是CTE因为您在2008年
6 S" R# F% G( K$ U uWITH transactionTotal AS( SELECT t.Created,t.Description,t.Credit,t.Debit,t.TransDate,t.TransactionID,a.AccountID ROW_NUMBER() OVER (ORDER BY TransDate ASC) AS RowNumber ( ISNULL(t.Credit,0) - ISNULL(t.Debit,0) ) AS TransactionTotal FROM dbo.Transactions AS t INNER JOIN dbo.BankAccounts AS a ON t.AccountID = a.AccountID)SELECT t.Created,t.Description,t.Credit,t.Debit,t.TransDate,t.TransactionID,t.AccountID ，( SELECT SUM(tt.TransactionTotal) FROM transactionTotal AS tt WHERE tt.RowNumber <= t.RowNumber) AS RunningTotalFROM transactionTotal AS tLEFT JOIN transactionTotal AS tt ON t.RowNumber = tt.RowNumber 1ORDER BY t.TransDate DESC |
|
技术问答
177 人阅读
|
0 人回复
|
2023-09-13
