有一总余额值1000
表A
payMoney(支出钱数) paydate(支出时间)
200 2010-1-15
150 2010-1-15
451 2010-2-20
45458 2010-2-21查询每次支出后的余额?
1000是当前余额
要像下面这样的结果
payMoney paydate tmoney(余额)
200 2010-1-15 46808
150 2010-1-15 46608
451 2010-2-20 46458
45458 2010-2-21 1000
表A
payMoney(支出钱数) paydate(支出时间)
200 2010-1-15
150 2010-1-15
451 2010-2-20
45458 2010-2-21查询每次支出后的余额?
1000是当前余额
要像下面这样的结果
payMoney paydate tmoney(余额)
200 2010-1-15 46808
150 2010-1-15 46608
451 2010-2-20 46458
45458 2010-2-21 1000
if object_id('tempdb.dbo.#') is not null drop table #
create table #(id int identity, payMoney int, paydate datetime)
insert into #
select 200, '2010-1-15' union all
select 150, '2010-1-15' union all
select 450, '2010-2-20' union all
select 45458, '2010-2-21'select *, (select isnull(sum(payMoney)+1000,1000) from # where id>t.id)tmoney from # t/*
id payMoney paydate tmoney
----------- ----------- ----------------------- -----------
1 200 2010-01-15 00:00:00.000 47058
2 150 2010-01-15 00:00:00.000 46908
3 450 2010-02-20 00:00:00.000 46458
4 45458 2010-02-21 00:00:00.000 1000
*/
(
PAYMONEY INT,
PAYDATE DATETIME
)
INSERT #
SELECT 200, '2010-1-15' UNION ALL
SELECT 150, '2010-1-15' UNION ALL
SELECT 450, '2010-2-20' UNION ALL
SELECT 45458, '2010-2-21';WITH cte_temp AS
(
SELECT rowno = ROW_NUMBER() OVER(ORDER BY paydate DESC), * FROM #
)
SELECT a.payMoney, a.paydate, 余额 = 1000 + ISNULL(余额, 0) FROM
(SELECT * FROM cte_temp) a
CROSS APPLY
(SELECT 余额 = SUM(payMoney) FROM cte_temp WHERE rowno < a.rowno) b
ORDER BY a.rowno DESC
sum(money)+1000 from tb where id>t.id
create table #(id int identity, payMoney int, paydate datetime)
insert into #
select 200, '2010-1-15' union all
select 150, '2010-1-15' union all
select 450, '2010-2-20' union all
select 45458, '2010-2-21'DECLARE @num INT
SET @num=1000SELECT t.payMoney,payDate=convert(VARCHAR(10),t.paydate,120),
tmoney=@num+sum(isnull(k.payMoney,0))
FROM # t LEFT JOIN # k ON t.id<k.id
GROUP BY t.payMoney,t.paydate
/*
payMoney payDate tmoney
-------- ---------- ------
150 2010-01-15 46908
200 2010-01-15 47058
450 2010-02-20 46458*/
消息 4104,级别 16,状态 1,第 46 行
无法绑定由多个部分组成的标识符 "t.id"。
这里的t是从哪来的哟