这样的select语句怎么写？

[cupwei]

现在有这样的数据表结构：
zid data
1 0.5
2 1.0
3 1.5
4 3.5

现在想将第二行开始将data相加 请教下这个select 语句怎么写？

[王向飞]

--> 数据库版本:
--> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22
--> 测试数据：[TB]
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TB]') 
AND type in (N'U')) 
DROP TABLE [TB]
GO

---->建表
create table [TB]([zid] int,[data] numeric(2,1))
insert [TB]
select 1,0.5 union all
select 2,1.0 union all
select 3,1.5 union all
select 4,3.5
GO



--> 查询结果
SELECT zid,case when zid= 1 then data else data+1 end  
FROM [TB]
--> 删除表格
--DROP TABLE [TB]

[华夏小卒]

--> 测试数据: #tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb (zid int,data numeric(2,1))
insert into #tb
select 1,0.5 union all
select 2,1.0 union all
select 3,1.5 union all
select 4,3.5

select zid,
data=(select sum(data) from #tb where zid<=t.zid) 
from #tb t

zid         data
----------- ---------------------------------------
1           0.5
2           1.5
3           3.0
4           6.5

(4 行受影响)

???

[supersunshine]

你想将结果显示成什么样子的？

[王向飞]

--> 数据库版本:
--> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22
--> 测试数据：[TB]
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TB]') 
AND type in (N'U')) 
DROP TABLE [TB]
GO

---->建表
create table [TB]([zid] int,[data] numeric(2,1))
insert [TB]
select 1,0.5 union all
select 2,1.0 union all
select 3,1.5 union all
select 4,3.5
GO



--> 查询结果

select [zid] ,
       [data] =(select sum(isnull([data],0)) from [TB] where zid <= a.[zid])
from [TB] a  
--> 删除表格
--DROP TABLE [TB]

--zid	data
1	0.5
2	1.5
3	3.0
4	6.5

[振乾]

[Quote=引用 2 楼 js_szy 的回复:]
SQL code
--> 测试数据: #tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb (zid int,data numeric(2,1))
insert into #tb
select 1,0.5 union all
select 2,1.0 union all
s……
[/Quote]

正解 支持。

[cupwei]

我意思是比如是四行 我就将后三行显示并相加 如果有五行就将后四行相加。。。以此类推

[cupwei]

我的意思就是数据库中有内行 我就显示除去第一行以外的n-1行 但是id不一定是从1开始的

[王向飞]

最后只显示两行？？
第一行
union
你的sum()累加行？
id只是一个演示，你可以使用row_number（）替换或用临时表替换，一个道理。

[renwenli07461]

SELECT SUM(data)-(SELECT DISTINCT [data] FROM [表名] WHERE zid=(SELECT MIN(zid) FROM [表名]) ) FROM [表名]

[abuying]

create table [#TB]([zid] int,[data] numeric(2,1))
insert [#TB]
select 1,0.5 union all
select 2,1.0 union all
select 3,1.5 union all
select 4,3.5
GO

select *,sumData=(select isnull(sum(data),0) from  #tb t where  t.zid<#tb.zid) from #tb
--t.zid<#tb.zid
/**
zid         data                                    sumData
----------- --------------------------------------- ---------------------------------------
1           0.5                                     0.0
2           1.0                                     0.5
3           1.5                                     1.5
4           3.5                                     3.0

(4 行受影响)
**/

[abuying]

create table [#TB]([zid] int,[data] numeric(2,1))
insert [#TB]
select 1,0.5 union all
select 2,1.0 union all
select 3,1.5 union all
select 4,3.5
GO

select *,sumData=(select isnull(sum(data),0) from  #tb t where  t.zid<#tb.zid) from #tb
--t.zid<#tb.zid
/**
zid         data                                    sumData
----------- --------------------------------------- ---------------------------------------
1           0.5                                     0.0
2           1.0                                     0.5
3           1.5                                     1.5
4           3.5                                     3.0

(4 行受影响)
**/