select * from (
select * from 表A a
where id=(select min(id) from 表A where NA=a.Na)
union all
select ID,NA,SY,(select HJ from 表A b where b.NA=a.Na and b.id=(select min(id) from 表A where NA=b.Na))+(select sum(SY) from 表A where NA=a.NA and id<=a.id and id<>(select min(id) from 表A where NA=b.Na)) as HJ from 表A a
where id<>(select min(id) from 表A where NA=a.Na)
) as x
order by Na,Id
select * from 表A a
where id=(select min(id) from 表A where NA=a.Na)
union all
select ID,NA,SY,(select HJ from 表A b where b.NA=a.Na and b.id=(select min(id) from 表A where NA=b.Na))+(select sum(SY) from 表A where NA=a.NA and id<=a.id and id<>(select min(id) from 表A where NA=b.Na)) as HJ from 表A a
where id<>(select min(id) from 表A where NA=a.Na)
) as x
order by Na,Id
解决方案 »
- 张利国_Java高端培训系列视频_oracle数据库
- 请大家介绍一本系统的介绍sql2000的书,比较全面一点的 入门一类的就算了
- 最后10分请教一个问题。谢谢。求一个存储过程
- 怎么找出相同记录或不同记录?
- 网卡IP绑定
- 数据库的迁移
- 高手请进,小弟问一个比较有点难度的问题?
- Sqlserver怎么会有这个毛病啊?真恶心!
- SQL Server2000中下面一条语句竟然错误?郁闷
- '****************junglerover(灌木丛)的问题:MSDE2000是否确实是SQL SERVER 2000的免费版本?*********************
- 请问SQL SERVER里面怎么定义和ACCESS一样的自动ID呢?
- 查询问题:相同的Field1,Field2只取一条纪录,但是要返回Field1,Field2,Filed3,Field4等多个字段?
select * from (
select * from 表A a
where id=(select min(id) from 表A where NA=a.Na)
union all
select ID,NA,SY,(select HJ from 表A b where b.NA=a.Na and b.id=(select min(id) from 表A where NA=a.Na))+(select sum(SY) from 表A where NA=a.NA and id<=a.id and id<>(select min(id) from 表A where NA=a.Na)) as HJ from 表A a
where id<>(select min(id) from 表A where NA=a.Na)
) as x
order by Na,Id
update B set B.HJ = A.HJ - A.sy from B, A where B.NA = A.NA and A.id = (select min(id) from A where NA = B.NA)
update B set HJ = B.HJ + isnull((select sum(A.sy) from A where NA = B.NA and ID <= B.id),0) from Bselect * from B order by NA
第二种解法只能运行一次,并且在将它转为VIEW时出错
还有别的方法吗?
我想建立表B一样的VIEW
可以吧?
我只想建立表B一样的VIEW
可以吗?
select id,na,sy,hj+isnull((select sum(sy) from a where na=aa.na and id>aa.id ),0) from a as aa order by Na,Id
但出来的结果只是在第一笔记录上而不是一条一条笔记录的计算出来
select * into B from Aupdate b
set sy=a.hj
from (select * from b where hj=(select min(hj) from b a where a.na=b.na) )a
where b.id=a.id
select id,na,sy,sy+isnull((select sum(sy) from b where id<aa.id and na=aa.na),0) from b aa
order by na,id
select * into B from Aupdate b
set sy=a.hj
from (select * from b where hj=(select min(hj) from b a where a.na=b.na) )a
where b.id=a.id
select id,na,sy,sy+isnull((select sum(sy) from b where id<aa.id and na=aa.na),0) from b aa
order by na,id
测试了j老师,老衲和海老大的代码 结果和你们一样
环境 win2k server(chn)+sql2k enterprise(chn)+sp3大力你的那个我是了两台机器 另一台为2k pro +个人版+无sp3
结果都是
YOki 44 57 90 191 1
Tom 0 45 99 144 2没想到union all 会有这个问题--处理过程:
--select f1,f5,identity(int,1,1) as id into #t from (
select * from (
select f1,f5 from @a
union all
select f1,f6 from @a
union all
select f1,f7 from @a
union all
select f1,f8 from @a
) A --order by f1
1 14
2 3
1 6
2 45
1 13
2 25
1 25
2 8--select f1,f5,identity(int,1,1) as id into #t from (
select * from (
select f1,f5 from @a
union
select f1,f6 from @a
union
select f1,f7 from @a
union
select f1,f8 from @a
) A -- order by f1
--exec('alter table #t add id int identity(1,1)')
1 6
1 13
1 14
1 25
2 3
2 8
2 25
2 45union 和union all 取记录的方式不同
union 是按顺序先取第1个记录再第二个
union all 则是交叉取记录
select distinct(na) from 表b order by nadeclare @na1 varchar(50)
declare @a intopen mycursorfetch first from mycursor into @na1while @@fetch_status=0
begin
select @a=count(*) from 表b where na=@na1if @a>1
begin
declare mycursor1 cursor scroll dynamic for
select id,hj from 表b where na=@na1 order by na
declare @b int
declare @b1 int
declare @id1 int
open mycursor1
fetch first from mycursor1 into @id1,@b
while @@fetch_status=0
begin
fetch next from mycursor1 into @id1,@b1
update 表b
set hj=sy+@b
where hj=@b1 and id=@id1
select @b=hj from 表b where hj=sy+@b and id=@id1
end
close mycursor1
deallocate mycursor1
end fetch next from mycursor into @na1
endclose mycursor
deallocate mycursor
我只想用一个VIEW查询出来
不知可不可以
你在最后可以加
create view 视图名 as
select * from #表b
这样子就只有一个视图了.临时表会自动被删除.