--> 数据库版本: --> 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.5select zid, data=(select sum(data) from #tb where zid<=t.zid) from #tb tzid data ----------- --------------------------------------- 1 0.5 2 1.5 3 3.0 4 6.5(4 行受影响)???
--> 数据库版本: --> 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
我的意思就是数据库中有内行 我就显示除去第一行以外的n-1行 但是id不一定是从1开始的
最后只显示两行?? 第一行 union 你的sum()累加行? id只是一个演示,你可以使用row_number()替换或用临时表替换,一个道理。
SELECT SUM(data)-(SELECT DISTINCT [data] FROM [表名] WHERE zid=(SELECT MIN(zid) FROM [表名]) ) FROM [表名]
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 GOselect *,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 行受影响) **/
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 GOselect *,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 行受影响) **/
--> 数据库版本:
--> 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]
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.5select zid,
data=(select sum(data) from #tb where zid<=t.zid)
from #tb tzid data
----------- ---------------------------------------
1 0.5
2 1.5
3 3.0
4 6.5(4 行受影响)???
--> 数据库版本:
--> 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
第一行
union
你的sum()累加行?
id只是一个演示,你可以使用row_number()替换或用临时表替换,一个道理。
insert [#TB]
select 1,0.5 union all
select 2,1.0 union all
select 3,1.5 union all
select 4,3.5
GOselect *,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 行受影响)
**/
insert [#TB]
select 1,0.5 union all
select 2,1.0 union all
select 3,1.5 union all
select 4,3.5
GOselect *,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 行受影响)
**/