求sql_增加新字段用于合计

[wukankan52]

表：
name,remain
A 10
B 8
C 15

create table CK(
Name nvarchar(100),Remain integer)
go
insert into ck VALUES('A',10)
insert into ck VALUES('B',8)
insert into ck VALUES('C',15)

需要得到一个新表，按remain从小大排序，且增加一个字段显示到该条纪录止remain的合计数
得到结果为：
name,remain,total
B 8 8
A 10 18 --=8+10
C 15 33 --=8+10+15
怎么写sql

[liangCK]

SELECT
    Name,
    Remain,
    (SELECT SUM(Remain) FROM CK WHERE Remain<=A.Remain) AS total
FROM CK AS A
ORDER BY Remain

[sdhdy]

select a.name,a.remain,total=(select sum(remain) from ck where remain<=a.remain) from CK a order by remain

[sdhdy]

select a.name,a.remain,total=(select sum(remain) from ck where remain<=a.remain) from CK a order by remain
/*
name                                                                                                 remain      total       
---------------------------------------------------------------------------------------------------- ----------- ----------- 
B                                                                                                    8           8
A                                                                                                    10          18
C                                                                                                    15          33
*/

[htl258_Tony]

create table CK( 
Name nvarchar(100),Remain  integer) 
go 
insert into ck VALUES('A',10) 
insert into ck VALUES('B',8) 
insert into ck VALUES('C',15) 

select * into # from ck order by remain

select *,total=(select sum(remain) from ck where remain<=a.remain) from # a

drop table ck,#

Name                                                                                                 Remain      total       
---------------------------------------------------------------------------------------------------- ----------- ----------- 
B                                                                                                    8           8
A                                                                                                    10          18
C                                                                                                    15          33

（所影响的行数为 3 行）

[claro]

帮顶

[claro]

/*表：
name,remain
A 10
B 8
C 15

create table CK(
Name nvarchar(100),Remain  integer)
go
insert into ck VALUES('A',10)
insert into ck VALUES('B',8)
insert into ck VALUES('C',15)

需要得到一个新表，按remain从小大排序，且增加一个字段显示到该条纪录止remain的合计数
得到结果为：
name,remain,total
B  8  8
A  10  18  --=8+10
C  15  33  --=8+10+15
怎么写sql 
*/
select [NAME],remain,(select sum(remain) from ck where remain<=a.remain) as sum_remain
 from ck a
order by 2
NAME	remain	sum_remain
B	8	8
A	10	18
C	15	33

[ai_li7758521]

if object_id('ck') is not null drop table ck

create table CK(Name nvarchar(100),Remain  integer) 

insert  ck 
select 'A',10 union all
select 'B',8  union all
select 'C',15 


select name,remain,total=(select sum(remain) from ck where remain<=A.remain)
into YourNewTable 
from CK A
order by remain

select * from YourNewTable

/*
drop table YourNewTable

name      remain      total
-------- ----------- -----------
B         8           8
A         10          18
C         15          33

(3 行受影响)*/