表如下
name balance
---------------
zs 100
ls 200
ww 300
zl 400
qq 500我要查出的格式如下
zs 100
ls 300
ww 600
zl 1000
zl 1500
也就是每次都把前面的余额相加,再加上自己的余额
name balance
---------------
zs 100
ls 200
ww 300
zl 400
qq 500我要查出的格式如下
zs 100
ls 300
ww 600
zl 1000
zl 1500
也就是每次都把前面的余额相加,再加上自己的余额
解决方案 »
- 一个分数字段,有个1、2位小数,小数也是固定0.25,0.5,0.75,用什么类型合理?
- 关于父子结构的表 转变为层级结构的Web 页面
- 请教一个sql语句的优化,我在子查询中用到count(distinct)的时候,效率低的不行
- 如何在fluidsim中通过PC Access和s7-200plc联接
- compute子句在编程的时候,有什么用处??
- ALTER COLUMN的问题,还望指教!
- 有關事務的問題!
- sql server客户端安装失败??
- 查询
- 数据库事务的简单问题
- 存储过程里面如何判断插入时间段是否在数据库中已存在
- microsoft visual foxpro sql中如何写case when?
SELECT NAME,(SELECT SUM(balance ) FROM #T WHERE ID<=T.ID)AS balance FROM #TT这样应该好点
SELECT NAME,(SELECT SUM(balance ) FROM #T WHERE ID<=T.ID)AS balance FROM #T T
后面那两个跟紧了,修改证
select
[name],
(select sum(balance)from ta where id<=t.id)balance from tb t) as balance
from
tb
create table [tb]([name] varchar(2),[balance] int)
insert [tb]
select 'zs',100 union all
select 'ls',200 union all
select 'ww',300 union all
select 'zl',400 union all
select 'qq',500select [name],balance,
合计=(select sum(balance) from tb where balance<=a.balance )
from tb a
/*
name balance 合计
---- ----------- -----------
zs 100 100
ls 200 300
ww 300 600
zl 400 1000
qq 500 1500(5 行受影响)
*/
select
[name],
(select sum(balance)from ta where id<=t.id)balance from tb t) as balance
from
ta t
declare @T table(name VARCHAR(10),balance int)
Insert @T select
'zs', 100 union all select
'ls' , 200 union all select
'ww' , 300 union all select
'zl' , 400 union all select
'qq' , 500 SELECT NAME,BALANCE=(SELECT SUM(BALANCE) FROM @T WHERE BALANCE<=T.BALANCE) FROM @T TNAME BALANCE
---------- -----------
zs 100
ls 300
ww 600
zl 1000
qq 1500(5 行受影响)
if object_id('[s]') is not null drop table [s]
create table [s] (name varchar(2),balance int)
insert into [s]
select 'zs',100 union all
select 'ls',200 union all
select 'ww',300 union all
select 'zl',400 union all
select 'qq',500select id=identity(int,1,1),* into # from [s]
select name,balance=(select sum(balance) from # where id<=a.id)
from # a--结果:
name balance
---- -----------
zs 100
ls 300
ww 600
zl 1000
qq 1500
if object_id('[tb]') is not null drop table [tb]
create table [tb]([name] varchar(2),[balance] int)
insert [tb]
select 'zs',100 union all
select 'ls',200 union all
select 'ww',300 union all
select 'zl',400 union all
select 'qq',500select name,
balance=(select sum(balance) from tb where name<=a.name)
from tb a
-->Author:wufeng4552【水族杰纶】
-->Date :2009-08-24 09:27:29
declare @t table([name] nvarchar(2),[balance] int)
Insert @t
select N'zs',100 union all
select N'ls',200 union all
select N'ww',300 union all
select N'zl',400 union all
select N'qq',500
;with test
as
(Select *,ID=row_number() over (order by getdate())from @t)
select [name],
balance,
total=(select sum([balance])from test where id<=t.id)
from test t
/*
name balance total
---- ----------- -----------
zs 100 100
ls 200 300
ww 300 600
zl 400 1000
qq 500 1500(5 個資料列受到影響)
*/
if object_id('test') is not null
drop table testcreate table test
(
name varchar(10),
balance int
)insert test
select 'zs',100 union all
select 'ls',200 union all
select 'ww',300 union all
select 'zl',400 union all
select 'qq',500 select name,(select sum(balance) from test where balance <= t.balance) from test t/**
name
---------- -----------
zs 100
ls 300
ww 600
zl 1000
qq 1500(所影响的行数为 5 行)
**/