34,593
社区成员
发帖
与我相关
我的任务
分享
IF object_id('tb')IS NOT NULL DROP TABLE tb
CREATE TABLE tb (name VARCHAR(3),id INT )
INSERT tb
SELECT 'a', 1 UNION ALL
SELECT 'b' ,4 UNION ALL
SELECT 'c', 12 UNION ALL
SELECT 'd' ,6
SELECT * FROM tb
WITH romance
AS
(
SELECT rn=row_number()OVER (ORDER BY name),name,id FROM tb)
SELECT name,
id=(SELECT sum(id) FROM romance WHERE rn<=a.rn)
FROM romance a
select a.column1,sum(a.column2) over() - a.column2 sumval
from tb a
order by a.column1
create table tb(id int , c1 varchar(10), c2 int)
insert into tb values(1,'a', 1)
insert into tb values(2,'b', 4)
insert into tb values(3,'c', 12)
insert into tb values(4,'d', 6)
go
select c1 ,
c2 = (select sum(c2) from tb where c1 <= t.c1)
from tb t
/*
c1 c2
---------- -----------
a 1
b 5
c 17
d 23
(所影响的行数为 4 行)
*/
select c1 ,
c2 = (select sum(c2) from tb where id <= t.id)
from tb t
/*
c1 c2
---------- -----------
a 1
b 5
c 17
d 23
(所影响的行数为 4 行)
*/
drop table tb
select a.column1,sum(a.column2) - a.column2
from tb a
group by a.column1
order by a.column1
create table tb(c1 varchar(10), c2 int)
insert into tb values('a', 1)
insert into tb values('b', 4)
insert into tb values('c', 12)
insert into tb values('d', 6)
go
select c1 ,
c2 = (select sum(c2) from tb where c1 <= t.c1)
from tb t
drop table tb
/*
c1 c2
---------- -----------
a 1
b 5
c 17
d 23
(所影响的行数为 4 行)
*/