IF NOT OBJECT_ID('tb') IS NULL
DROP TABLE tb
create table tb (a1 int,a3 int ,a4 varchar(50))
insert into tb select 01, 100 ,'2011-10-01'
insert into tb select 02, 100 ,'2011-10-01'
insert into tb select 03, 80 ,'2011-10-02'
insert into tb select 05, 30 ,'2011-11-01'
insert into tb select 05, 200,'2011-10-01'
insert into tb select 06, 300 ,'2011-11-02'/*我想得到的结果是:
a1 a3 a4
----------- ----------- --------------------------------------------------
1 100 2011-10-01
2 200 2011-10-01
5 400 2011-10-01
3 480 2011-10-02
5 510 2011-11-01
6 810 2011-11-02请问要如何写sql查询语句,不支持row_number().
*/
DROP TABLE tb
create table tb (a1 int,a3 int ,a4 varchar(50))
insert into tb select 01, 100 ,'2011-10-01'
insert into tb select 02, 100 ,'2011-10-01'
insert into tb select 03, 80 ,'2011-10-02'
insert into tb select 05, 30 ,'2011-11-01'
insert into tb select 05, 200,'2011-10-01'
insert into tb select 06, 300 ,'2011-11-02'/*我想得到的结果是:
a1 a3 a4
----------- ----------- --------------------------------------------------
1 100 2011-10-01
2 200 2011-10-01
5 400 2011-10-01
3 480 2011-10-02
5 510 2011-11-01
6 810 2011-11-02请问要如何写sql查询语句,不支持row_number().
*/
insert into tb select 01, 100 ,'2011-10-01'
insert into tb select 02, 100 ,'2011-10-01'
insert into tb select 03, 80 ,'2011-10-02'
insert into tb select 05, 30 ,'2011-11-01'
insert into tb select 05, 200,'2011-10-01'
insert into tb select 06, 300 ,'2011-11-02'
go
select *,n=identity(int,1,1) into #t from tb order by a4,a1
select a1,(select sum(a3) from #t where n<=a.n)as a3,a4 from #t a
/*
a1 a3 a4
----------- ----------- --------------------------------------------------
1 100 2011-10-01
2 200 2011-10-01
5 400 2011-10-01
3 480 2011-10-02
5 510 2011-11-01
6 810 2011-11-02(6 行受影响)*/
go
drop table tb,#t
insert into tb select 01, 100 ,'2011-10-01'
insert into tb select 02, 100 ,'2011-10-01'
insert into tb select 03, 80 ,'2011-10-02'
insert into tb select 05, 30 ,'2011-11-01'
insert into tb select 05, 200,'2011-10-01'
insert into tb select 06, 300 ,'2011-11-02'
go
select a1,(select sum(a3) from tb where a4<a.a4 or(a4=a.a4 and a1<=a.a1))as a3,a4 from tb a order by a4,a1
/*
a1 a3 a4
----------- ----------- --------------------------------------------------
1 100 2011-10-01
2 200 2011-10-01
5 400 2011-10-01
3 480 2011-10-02
5 510 2011-11-01
6 810 2011-11-02(6 行受影响)*/
go
drop table tb
a1,
a3=(select sum(a3) from tb where a4+RTRIM(a1)<=t.a4+RTRIM(t.a1)),
a4
from tb t
order by a4,a1
if object_id('[tb]') is not null drop table [tb]
go
create table tb (a1 int,a3 int ,a4 varchar(50))
insert into tb select 01, 100 ,'2011-10-01'
insert into tb select 02, 100 ,'2011-10-01'
insert into tb select 03, 80 ,'2011-10-02'
insert into tb select 05, 30 ,'2011-11-01'
insert into tb select 05, 200,'2011-10-01'
insert into tb select 06, 300 ,'2011-11-02'
select a1,a3=(select sum(a3) from tb where (a4=t.a4 and a1<=t.a1) or a4<t.a4),a4 from [tb] t order by a4,a1
/*
a1 a3 a4
----------- ----------- --------------------------------------------------
1 100 2011-10-01
2 200 2011-10-01
5 400 2011-10-01
3 480 2011-10-02
5 510 2011-11-01
6 810 2011-11-02(6 行受影响)*/
(select sum(a3) from atd.test_1201_2 t2 where t2.a4<t1.a4 or(t2.a4=t1.a4 and t2.a1<=t1.a1))a3,
t1.a4
from atd.test_1201_2 t1
order by a4;