表 A khid 客户id ,khname 客户名称, dnumber 单号,ddate 单据日期, yszk 应收帐款,sk 收款数.
表中排列顺序不一.没有主键.(实际是视图得来的)
khid khname dnumber ddate yszk sk
1 客户A 001 20071101 5400 0
2 客户B 002 20070101 2800 0
1 客户A 003 20070202 0 200
1 客户A 003 20070202 0 400
2 客户B 004 20070203 0 900
2 客户B 005 20070203 0 300
1 客户A 006 20070301 3600 0
2 客户B 007 20070302 900 0想要的结果:增加一个字段jy,jy=yszk-sk,第二行jy=上一行jy+yszk-sk,(可以是存储过程或者视图)
khid khname dnumber ddate yszk sk jy
1 客户A 001 20071101 5400 0 5400
1 客户A 003 20070202 0 200 5200
1 客户A 003 20070202 0 400 6800
1 客户A 006 20070301 3600 0 10400
2 客户B 002 20070101 2800 0 2800
2 客户B 004 20070203 0 900 1800
2 客户B 005 20070203 0 300 1600
2 客户B 007 20070302 900 0 2500
表中排列顺序不一.没有主键.(实际是视图得来的)
khid khname dnumber ddate yszk sk
1 客户A 001 20071101 5400 0
2 客户B 002 20070101 2800 0
1 客户A 003 20070202 0 200
1 客户A 003 20070202 0 400
2 客户B 004 20070203 0 900
2 客户B 005 20070203 0 300
1 客户A 006 20070301 3600 0
2 客户B 007 20070302 900 0想要的结果:增加一个字段jy,jy=yszk-sk,第二行jy=上一行jy+yszk-sk,(可以是存储过程或者视图)
khid khname dnumber ddate yszk sk jy
1 客户A 001 20071101 5400 0 5400
1 客户A 003 20070202 0 200 5200
1 客户A 003 20070202 0 400 6800
1 客户A 006 20070301 3600 0 10400
2 客户B 002 20070101 2800 0 2800
2 客户B 004 20070203 0 900 1800
2 客户B 005 20070203 0 300 1600
2 客户B 007 20070302 900 0 2500
解决方案 »
- SQLSERVER2008安装出错--错误代码 0x84B10001
- in的问题
- 复制订阅时系统自动插入msrepl_tran_version字段
- 请教sql
- 从SMALLDATETIME类型字段中取出年和月?
- 下面的这个如何处理
- 关于数据库备份的问题,简单的,指点一下
- 这段代码有什么问题????????
- ★★★100分求助:sql server2000中文版中如何设置,使得select * from testtable where testfield like '____China'能够查询到字符串'你
- ado访问sqlserver2000 问题
- 热心人也帮我写一个SQL查询语句
- 请教一个查询语句如何写?
khid khname dnumber ddate yszk sk jy
1 客户A 001 20071101 5400 0 5400
1 客户A 003 20070202 0 200 5200
1 客户A 003 20070202 0 400 6800
1 客户A 006 20070301 3600 0 10400
2 客户B 002 20070101 2800 0 2800
2 客户B 004 20070203 0 900 1800
2 客户B 005 20070203 0 300 1600
2 客户B 007 20070302 900 0 2500
-----------------
楼主第三行的是怎么算出6800的?
khid khname dnumber ddate yszk sk jy
1 客户A 001 20071101 5400 0 5400
1 客户A 003 20070202 0 200 5200
1 客户A 003 20070202 0 400 4800
1 客户A 006 20070301 3600 0 8400
select
*,
row=identity(int,1,1)
into #
from
a
order by ddate asc--指定ddate或dnumber--------select
*,
[jy]=(select sum(yszk-sk) from # where row!>t.row)
from
# t
order by khid,khname
--khid,khname定义这个排序
select
*,
row=identity(int,1,1)
into #
from
a
order by khid,khname,ddate asc--指定ddate或dnumber--------select
*,
[jy]=(select sum(yszk-sk) from # where row!>t.row)
from
# t
order by khid,khname
想要的结果:增加一个字段jy,jy=yszk-sk,第二行jy=上一行jy+yszk-sk,(可以是存储过程或者视图)
khid khname dnumber ddate yszk sk jy
1 客户A 001 20071101 5400 0 5400
1 客户A 003 20070202 0 200 5200
1 客户A 003 20070202 0 400 4800
1 客户A 006 20070301 3600 0 8400
2 客户B 002 20070101 2800 0 2800
2 客户B 004 20070203 0 900 1900
2 客户B 005 20070203 0 300 1600
2 客户B 007 20070302 900 0 2500
yszk int, sk int)
insert a select 1, '客户A', '001', '20071101', 5400, 0
insert a select 2, '客户B', '002', '20070101', 2800, 0
insert a select 1, '客户A', '003', '20070202', 0 , 200
insert a select 1, '客户A', '003', '20070202', 0 , 400
insert a select 2, '客户B', '004', '20070203', 0 , 900
insert a select 2, '客户B', '005', '20070203', 0 , 300
insert a select 1, '客户A', '006', '20070301', 3600 , 0
insert a select 2, '客户B', '007', '20070302', 900 , 0
------------------------khid,khname定义这个排序
select
*,
row=identity(int,1,1)
into #
from
a
order by khid,khname,dnumber asc--dnumber--------select
*,
[jy]=(select sum(yszk-sk) from # where row!>t.row)
from
# t
order by khid,khname
khid khname dnumber ddate yszk sk row jy
----------- ---------- ------- ------------------------------------------------------ ----------- ----------- ----------- -----------
1 客户A 001 2007-11-01 00:00:00.000 5400 0 1 5400
1 客户A 003 2007-02-02 00:00:00.000 0 200 2 5200
1 客户A 003 2007-02-02 00:00:00.000 0 400 3 4800
1 客户A 006 2007-03-01 00:00:00.000 3600 0 4 8400
2 客户B 002 2007-01-01 00:00:00.000 2800 0 5 11200
2 客户B 004 2007-02-03 00:00:00.000 0 900 6 10300
2 客户B 005 2007-02-03 00:00:00.000 0 300 7 10000
2 客户B 007 2007-03-02 00:00:00.000 900 0 8 10900(所影响的行数为 8 行)
create table test1(khid int,khname varchar(20),dnumber varchar(20),ddate varchar(20),yszk int,sk int)
insert into test1 select 1,'客户A','001','20071101',5400, 0
insert into test1 select 2,'客户B','002','20070101',2800, 0
insert into test1 select 1,'客户A','003','20070202',0,200
insert into test1 select 1,'客户A','003','20070202',0,400
insert into test1 select 2,'客户B','004','20070203',0,900
insert into test1 select 2,'客户B','005','20070203',0,300
insert into test1 select 1,'客户A','006','20070301',3600, 0
insert into test1 select 2,'客户B','007','20070302',900, 0 select id=identity(int,1,1),* into #temp from (select top 100 percent *,jy=yszk-sk from test1 order by khname,dnumber)aupdate #temp set jy=(select sum(jy) from #temp where id<=a.id and khid=a.khid) from #temp aselect * from #temp
用2005可用视图row_number
create table a(khid int, khname nvarchar(10), dnumber nvarchar(5), ddate datetime,
yszk int, sk int)
insert a select 1, '客户A', '001', '20071101', 5400, 0
insert a select 2, '客户B', '002', '20070101', 2800, 0
insert a select 1, '客户A', '003', '20070202', 0 , 200
insert a select 1, '客户A', '003', '20070202', 0 , 400
insert a select 2, '客户B', '004', '20070203', 0 , 900
insert a select 2, '客户B', '005', '20070203', 0 , 300
insert a select 1, '客户A', '006', '20070301', 3600 , 0
insert a select 2, '客户B', '007', '20070302', 900 , 0
------------------------khid,khname定义这个排序
select
*,
row=identity(int,1,1)
into #
from
a
order by khid,khname,dnumber asc--dnumber--------select
[khid],[khname],[dnumber],[ddate],[yszk],[sk],
[jy]=(select sum(yszk-sk) from # where row!>t.row)
from
# t
order by khid,khname
khid khname dnumber ddate yszk sk jy
----------- ---------- ------- ------------------------------------------------------ ----------- ----------- -----------
1 客户A 001 2007-11-01 00:00:00.000 5400 0 5400
1 客户A 003 2007-02-02 00:00:00.000 0 200 5200
1 客户A 003 2007-02-02 00:00:00.000 0 400 4800
1 客户A 006 2007-03-01 00:00:00.000 3600 0 8400
2 客户B 002 2007-01-01 00:00:00.000 2800 0 11200
2 客户B 004 2007-02-03 00:00:00.000 0 900 10300
2 客户B 005 2007-02-03 00:00:00.000 0 300 10000
2 客户B 007 2007-03-02 00:00:00.000 900 0 10900(所影响的行数为 8 行)
上面看错楼主要的结果了,用这个
--khid,khname定义这个排序
select
*,
row=identity(int,1,1)
into #
from
a
order by khid,khname,dnumber asc--dnumber--------select
[khid],[khname],[dnumber],[ddate],[yszk],[sk],
[jy]=(select sum(yszk-sk) from # where khid =t. khid and row!>t.row)
from
# t
order by khid,khnamekhid khname dnumber ddate yszk sk jy
----------- ---------- ------- ------------------------------------------------------ ----------- ----------- -----------
1 客户A 001 2007-11-01 00:00:00.000 5400 0 5400
1 客户A 003 2007-02-02 00:00:00.000 0 200 5200
1 客户A 003 2007-02-02 00:00:00.000 0 400 4800
1 客户A 006 2007-03-01 00:00:00.000 3600 0 8400
2 客户B 002 2007-01-01 00:00:00.000 2800 0 2800
2 客户B 004 2007-02-03 00:00:00.000 0 900 1900
2 客户B 005 2007-02-03 00:00:00.000 0 300 1600
2 客户B 007 2007-03-02 00:00:00.000 900 0 2500(所影响的行数为 8 行)
insert into #a values(1,'客户A','003','20070202',0,400)
insert into #a values(2,'客户B','004','20070203',0,900)
insert into #a values(2,'客户B','005','20070203',0,300)
insert into #a values(1,'客户A','006','20070301',3600,0)
insert into #a values(2,'客户B','007','20070302',900,0)
SELECT #a.*,jy=0 into #temp FROM #A order by khid,dnumberdeclare @khid int,@yszk int,@jy int
update #temp set @jy=case when khid=@khid then @jy+yszk-sk else yszk end,@khid=khid,jy=@jyselect * from #temp
(8 行受影响)
khid khname dnumber ddate yszk sk jy
----------- ---------- ---------- ---------- ----------- ----------- -----------
1 客户A 001 20071101 5400 0 5400
1 客户A 003 20070202 0 200 5200
1 客户A 003 20070202 0 400 4800
1 客户A 006 20070301 3600 0 8400
2 客户B 002 20070101 2800 0 2800
2 客户B 004 20070203 0 900 1900
2 客户B 005 20070203 0 300 1600
2 客户B 007 20070302 900 0 2500(8 行受影响)
insert into tb values(1, '客户A', '001', '20071101', 5400 , 0)
insert into tb values(2, '客户B', '002', '20070101', 2800 , 0)
insert into tb values(1, '客户A', '003', '20070202', 0 , 200)
insert into tb values(1, '客户A', '003', '20070202', 0 , 400)
insert into tb values(2, '客户B', '004', '20070203', 0 , 900 )
insert into tb values(2, '客户B', '005', '20070203', 0 , 300 )
insert into tb values(1, '客户A', '006', '20070301', 3600 , 0 )
insert into tb values(2, '客户B', '007', '20070302', 900 , 0 )
goselect id = identity(int , 1 , 1) , * into tmp from tb order by khid , dnumberselect * , jy = (select isnull(sum(yszk - sk),0) from tmp where khid = t.khid and id <= t.id) from tmp t
drop table tb ,tmp/*id khid khname dnumber ddate yszk sk jy
----------- ----------- ---------- ---------- ------------------------------------------------------ ----------- ----------- -----------
1 1 客户A 001 2007-11-01 00:00:00.000 5400 0 5400
2 1 客户A 003 2007-02-02 00:00:00.000 0 200 5200
3 1 客户A 003 2007-02-02 00:00:00.000 0 400 4800
4 1 客户A 006 2007-03-01 00:00:00.000 3600 0 8400
5 2 客户B 002 2007-01-01 00:00:00.000 2800 0 2800
6 2 客户B 004 2007-02-03 00:00:00.000 0 900 1900
7 2 客户B 005 2007-02-03 00:00:00.000 0 300 1600
8 2 客户B 007 2007-03-02 00:00:00.000 900 0 2500(所影响的行数为 8 行)*/
create table tb(khid int, khname varchar(10), dnumber varchar(10), ddate datetime, yszk int, sk int )
insert into tb values(1, '客户A', '001', '20071101', 5400 , 0)
insert into tb values(2, '客户B', '002', '20070101', 2800 , 0)
insert into tb values(1, '客户A', '003', '20070202', 0 , 200)
insert into tb values(1, '客户A', '003', '20070202', 0 , 400)
insert into tb values(2, '客户B', '004', '20070203', 0 , 900 )
insert into tb values(2, '客户B', '005', '20070203', 0 , 300 )
insert into tb values(1, '客户A', '006', '20070301', 3600 , 0 )
insert into tb values(2, '客户B', '007', '20070302', 900 , 0 )
goselect khid , khname , dnumber , ddate , yszk , sk , jy = (select isnull(sum(yszk - sk),0) from
(
select * , id = row_number() over(order by khid , dnumber) from tb
) m where khid = t.khid and id <= t.id)
from
(
select * , id = row_number() over(order by khid , dnumber) from tb
) tdrop table tb/*
khid khname dnumber ddate yszk sk jy
----------- ---------- ---------- ----------------------- ----------- ----------- -----------
1 客户A 001 2007-11-01 00:00:00.000 5400 0 5400
1 客户A 003 2007-02-02 00:00:00.000 0 200 5200
1 客户A 003 2007-02-02 00:00:00.000 0 400 4800
1 客户A 006 2007-03-01 00:00:00.000 3600 0 8400
2 客户B 002 2007-01-01 00:00:00.000 2800 0 2800
2 客户B 004 2007-02-03 00:00:00.000 0 900 1900
2 客户B 005 2007-02-03 00:00:00.000 0 300 1600
2 客户B 007 2007-03-02 00:00:00.000 900 0 2500(8 行受影响)
*/
*,
row=identity(int,1,1)
into #
from
a
order by khid,khname,ddate--------select
*,
[jy]=(select sum(yszk-sk) from # where row!>t.row)
from
# t
order by khid,khname
'----------正确答案-------------------------------
如果要按客户算的话 把[jy]=(select sum(yszk-sk) from # where row!>t.row)
改成[jy]=(select sum(yszk-sk) from # where row!>t.row and khname=t.khname)