dawgui 帮忙

Question

昨天哪个问题
如果我一张表里面有几组那样的数据,怎么进行分组那样更新???
----------------------------------------------------
update cwgl_tjbb_yfzk
   set sqye =
                isnull((select sum(bqzj) from cwgl_tjbb_yfzk where djrq < t.djrq),0) + 
                isnull((select sum(bqjs) from cwgl_tjbb_yfzk where djrq < t.djrq),0) ,
       bqye = 
                isnull((select sum(bqzj) from cwgl_tjbb_yfzk where djrq <= t.djrq),0) + 
                isnull((select sum(bqjs) from cwgl_tjbb_yfzk where djrq <= t.djrq),0)
from cwgl_tjbb_yfzk t where  t.bqjs<>0 and t.bqzj<>0  and t.khbh=(select distinct khbh from cwgl_tjbb_yfzk)

Answer 1

http://topic.csdn.net/u/20080927/09/3d3f03a4-1870-4716-af87-3b8b96b91cd7.html

Answer 2

数据表: 
-------------------------- 
日期,上期余额,现金1,现金2,本期余额,类型  --:上期余额为该条数据上面的本期余额,该表按日期大小排序 
----------------------------- 
例如: 
1)2008-9-25 00:00:00 ,0,12,12,24 ,1
2)2008-9-25 00:00:01 ,24,3,2,29 ,1
3)2008-9-25 00:01:00,29,4,-2,31 ,1
4)2008-9-25 01:00:00 ,31,2,2,35 ,1
5)2008-9-25 00:00:00 ,0,12,12,24 ,2
6)2008-9-25 00:00:01 ,24,3,2,29 ,2
7)2008-9-25 00:01:00,29,4,-2,31 ,2
8)2008-9-25 01:00:00 ,31,2,2,35 ,2
9).....这里想这样排序可能有N <500000条 
------------------------------- 
但是这是用户将中间的记录修改了 
例如:我将类型1的2)修改为: 
2)2008-9-25 00:00:01 ,24,4,2,30 ,1
后面页要依次类推的进行修改,为: 
3)2008-9-25 00:01:00,30,4,-2,32 ,1
4)2008-9-25 01:00:00 ,32,2,2,36 ,1
............后面类型为1的这样修改完 
我将类型为2的6)修改
6)2008-9-25 00:00:01 ,24,4,2,30 ,2
后面页要依次类推的进行修改,为: 
7)2008-9-25 00:01:00,30,4,-2,32 ,1
8)2008-9-25 01:00:00 ,32,2,2,36 ,1
...............后面类型为2的这样修改完  

Answer 3

update cwgl_tjbb_yfzk
   set sqye =
                isnull((select sum(bqzj) from cwgl_tjbb_yfzk where djrq < t.djrq),0) + 
                isnull((select sum(bqjs) from cwgl_tjbb_yfzk where djrq < t.djrq),0) ,
       bqye = 
                isnull((select sum(bqzj) from cwgl_tjbb_yfzk where djrq <= t.djrq),0) + 
                isnull((select sum(bqjs) from cwgl_tjbb_yfzk where djrq <= t.djrq),0)
from cwgl_tjbb_yfzk t where  (t.bqjs<>0 or t.bqzj<>0)  and t.khbh in (select distinct khbh from cwgl_tjbb_yfzk)
我想这样写着金额计算不对

Answer 4

表结够:
khbh, sqye, bqzj, bqjs, bqye, djrq
--------------------------------------
1)3,    0.00, 12,  -14.23, -2.23 ,2008-09-27 15:49:16.450
2)3,    -2.23,8,   0.00  ,6.23   ,2008-09-27 15:51:18.357
3)3,    6.23 ,10,  -13  ,  3.23  ,2008-09-28 09:18:26.670
4)3,    3.23, 0,   0    ,  3.23 ,2008-09-28 09:18:26.670
5)3,    3.23,12,  -3    ,  11.23,2008-09-28 09:37:59.903
6)4,    0.00, 12,  -14.23, -2.23 ,2008-09-27 15:49:16.450
7)4,    -2.23,8,   0.00  ,6.23   ,2008-09-27 15:51:18.357
8)4,    6.23 ,10,  -13  ,  3.23  ,2008-09-28 09:18:26.670
9)4,    3.23, 0,   0    ,  3.23 ,2008-09-28 09:18:26.670
10)4,    3.23,12,  -3    ,  11.23,2008-09-28 09:37:59.903   
--------------------------------------------------------------
现在我就是将2)修改成为
  2)3,    -2.23, 10,   0.00  ,8.23   ,2008-09-27 15:51:18.357
------------------------------------------------------------
  3)3,    8.23 ,10,  -13  ,  5.23  ,2008-09-28 09:18:26.670
  4)3,    3.23, 0,   0    ,  3.23 ,2008-09-28 09:18:26.670
  5)3,    5.23,12,  -3    ,  13.23,2008-09-28 09:37:59.903
..
如果bqzj=0, bqjs=0都为0则不进行相应计算同里我将7)修改
     7)4,    -2.23,8,   -1,5.23   ,2008-09-27 15:51:18.357
-----------------------------------------------------------
     8)4,    5.23 ,10,  -13  ,  2.23  ,2008-09-28 09:18:26.670
     9)4,    3.23, 0,   0    ,  3.23 ,2008-09-28 09:18:26.670
    10)4,    2.23,12,  -3    ,  10.23,2008-09-28 09:37:59.903
...
现在就是一个类型的数据按这样的情况排列?更新的时候忽略掉bqzj=0, bqjs=0全为0的行
我想那样改了 就是计算出错

Answer 5

update cwgl_tjbb_yfzk 
  set sqye = 
                isnull((select sum(bqzj) from cwgl_tjbb_yfzk where djrq < t.djrq),0) + 
                isnull((select sum(bqjs) from cwgl_tjbb_yfzk where djrq < t.djrq),0) , 
      bqye = 
                isnull((select sum(bqzj) from cwgl_tjbb_yfzk where djrq <= t.djrq),0) + 
                isnull((select sum(bqjs) from cwgl_tjbb_yfzk where djrq <= t.djrq),0) 
from cwgl_tjbb_yfzk t where  (t.bqjs <>0 or t.bqzj <>0)  and t.khbh in (select distinct khbh from cwgl_tjbb_yfzk)
我这样写
金额计算错误bqye

Answer 6

其实,你这个sqye,bqye这两个字段没有什么用处的,可以统计出来的,只需要bqzj, bqjs这两个字段即可.

Answer 7

--我想你可能有个数据搞错了,就是8.23 改为了7.77,其实你不改也无所谓,反正你这个数据是计算出来的.create table tb(khbh int, sqye decimal(18,2), bqzj  decimal(18,2), bqjs  decimal(18,2), bqye  decimal(18,2), djrq datetime)
insert into tb values(3, 0.00 ,12, -14.23, -2.23 ,'2008-09-27 15:49:16.450') 
insert into tb values(3, -2.23,8 , 0.00  , 6.23  ,'2008-09-27 15:51:18.357') 
insert into tb values(3, 6.23 ,10, -13   , 3.23  ,'2008-09-28 09:18:26.670') 
insert into tb values(3, 3.23 ,0 , 0     , 3.23  ,'2008-09-28 09:18:26.670') 
insert into tb values(3, 3.23 ,12, -3    , 11.23 ,'2008-09-28 09:37:59.903') 
insert into tb values(4, 0.00 ,12, -14.23, -2.23 ,'2008-09-27 15:49:16.450') 
insert into tb values(4, -2.23,8 , 0.00  , 6.23  ,'2008-09-27 15:51:18.357') 
insert into tb values(4, 6.23 ,10, -13   , 3.23  ,'2008-09-28 09:18:26.670') 
insert into tb values(4, 3.23 ,0 , 0     , 3.23  ,'2008-09-28 09:18:26.670') 
insert into tb values(4, 3.23 ,12, -3    , 11.23 ,'2008-09-28 09:37:59.903')
go
--原始数据
select * from tb
/*
khbh        sqye                 bqzj                 bqjs                 bqye                 djrq                                                   
----------- -------------------- -------------------- -------------------- -------------------- ------------------------------------------------------ 
3           .00                  12.00                -14.23               -2.23                2008-09-27 15:49:16.450
3           -2.23                8.00                 .00                  6.23                 2008-09-27 15:51:18.357
3           6.23                 10.00                -13.00               3.23                 2008-09-28 09:18:26.670
3           3.23                 .00                  .00                  3.23                 2008-09-28 09:18:26.670
3           3.23                 12.00                -3.00                11.23                2008-09-28 09:37:59.903
4           .00                  12.00                -14.23               -2.23                2008-09-27 15:49:16.450
4           -2.23                8.00                 .00                  6.23                 2008-09-27 15:51:18.357
4           6.23                 10.00                -13.00               3.23                 2008-09-28 09:18:26.670
4           3.23                 .00                  .00                  3.23                 2008-09-28 09:18:26.670
4           3.23                 12.00                -3.00                11.23                2008-09-28 09:37:59.903（所影响的行数为 10 行）
*/--现在我就是将2)修改成为 
--2)3,    -2.23, 10,  0.00  ,7.77  ,2008-09-27 15:51:18.357 
update tb 
set sqye = -2.23,
    bqzj = 10,
    bqjs = 0.00,
    bqye = 7.77
where khbh = 3 and djrq = '2008-09-27 15:51:18.357'update tb
set sqye = isnull((select sum(bqzj) from tb where khbh = t.khbh and (bqzj <> 0 or bqjs <> 0) and djrq < t.djrq),0) + 
           isnull((select sum(bqjs) from tb where khbh = t.khbh and (bqzj <> 0 or bqjs <> 0) and djrq < t.djrq),0) , 
    bqye = isnull((select sum(bqzj) from tb where khbh = t.khbh and (bqzj <> 0 or bqjs <> 0) and djrq <= t.djrq),0) + 
           isnull((select sum(bqjs) from tb where khbh = t.khbh and (bqzj <> 0 or bqjs <> 0) and djrq <= t.djrq),0) 
from tb t--查询修改后的数据
select * from tb
/*
khbh        sqye                 bqzj                 bqjs                 bqye                 djrq                                                   
----------- -------------------- -------------------- -------------------- -------------------- ------------------------------------------------------ 
3           .00                  12.00                -14.23               -2.23                2008-09-27 15:49:16.450
3           -2.23                10.00                .00                  7.77                 2008-09-27 15:51:18.357
3           7.77                 10.00                -13.00               4.77                 2008-09-28 09:18:26.670
3           7.77                 .00                  .00                  4.77                 2008-09-28 09:18:26.670
3           4.77                 12.00                -3.00                13.77                2008-09-28 09:37:59.903
4           .00                  12.00                -14.23               -2.23                2008-09-27 15:49:16.450
4           -2.23                8.00                 .00                  5.77                 2008-09-27 15:51:18.357
4           5.77                 10.00                -13.00               2.77                 2008-09-28 09:18:26.670
4           5.77                 .00                  .00                  2.77                 2008-09-28 09:18:26.670
4           2.77                 12.00                -3.00                11.77                2008-09-28 09:37:59.903（所影响的行数为 10 行）
*/
drop table tb