34,576
社区成员
发帖
与我相关
我的任务
分享
select
*,
利润=(select sum(a-b) from tb where workdate<=t.workdate)
from
tb
select *,
(select sum(a-b) from tb where workdate<=t.workdate)
as 利润 from tb t
---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([a] int,[b] int,[workdate] datetime)
insert [tb]
select 100,50,'2009-01-01' union all
select 200,150,'2009-01-02' union all
select 300,214,'2009-01-03' union all
select 0,0,'2009-01-03'
---查询---
select
*,
利润=(select sum(a-b) from tb where workdate<=t.workdate)
from
tb t
---结果---
a b workdate 利润
----------- ----------- ------------------------------------------------------ -----------
100 50 2009-01-01 00:00:00.000 50
200 150 2009-01-02 00:00:00.000 100
300 214 2009-01-03 00:00:00.000 186
0 0 2009-01-03 00:00:00.000 186
(所影响的行数为 4 行)
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([a] int,[b] int,[workdate] datetime)
insert [tb]
select 100,50,'20090101' union all
select 200,150,'20090102' union all
select 300,214,'20090103' union all
select 0,0,'20090104'
--select * from [tb]
select *, (select sum(a-b) from tb where workdate<=t.workdate) as 利润 from tb t
/*
a b workdate 利润
----------- ----------- ----------------------- -----------
100 50 2009-01-01 00:00:00.000 50
200 150 2009-01-02 00:00:00.000 100
300 214 2009-01-03 00:00:00.000 186
0 0 2009-01-04 00:00:00.000 186
(4 行受影响)
*/
select *, (select sum(a-b) from tb where workdate<=t.workdate) as 利润 from tb t
tb的别名
select
*,
(select sum(a-b) from tb where workdate<=t.workdate) as 利润
from
tb
select
*,
利润=(select sum(a-b) from tb where workdate<=t.workdate)
from
tb