表A 字段khid表示客户编号, yszk表示应收帐款
khid name yszk
1 客户A 10000
表B qtys 表示其他应收款
khid name qtys
1 客户A 8000
1 客户A 9000
1 客户A 888
表C sk表示已经收款数
khid name sk
1 客户A 899
1 客户A 999
要求视图结果: jy表示结余 jy=yszk+qtys-sk,第二行jy=第一行jy+yszk+qtys-sk
khid name yszk qtys SK jy
1 客户A 10000 0 0 10000
1 客户A 0 8000 0 18000
1 客户A 0 9000 0 27000
1 客户A 0 888 0 27888
1 客户A 0 0 899 26989
1 客户A 0 0 999 25990
khid name yszk
1 客户A 10000
表B qtys 表示其他应收款
khid name qtys
1 客户A 8000
1 客户A 9000
1 客户A 888
表C sk表示已经收款数
khid name sk
1 客户A 899
1 客户A 999
要求视图结果: jy表示结余 jy=yszk+qtys-sk,第二行jy=第一行jy+yszk+qtys-sk
khid name yszk qtys SK jy
1 客户A 10000 0 0 10000
1 客户A 0 8000 0 18000
1 客户A 0 9000 0 27000
1 客户A 0 888 0 27888
1 客户A 0 0 899 26989
1 客户A 0 0 999 25990
create view view_name
asselect khid,[name], yszk,0 as qtys,0 as SK
from ta
union all
select khid,[name], 0 as yszk,qtys ,0 as SK
from tb
union all
select khid,[name], 0 as yszk,0 as qtys ,SK
from tc 最后一列在2000中似乎不行,
2005有一个row_number ,是不是能试 试
insert TA
select 1,'客户A',10000
go
create table TB ( khid int, name varchar(10), qtys int)
insert TB
select 1 ,'客户A', 8000
union select 1 ,'客户A', 9000
union select 1 ,'客户A', 888
go
create table TC ( khid int, name varchar(10), sk int)
insert TC
select 1 ,'客户A', 899
union select 1 ,'客户A', 999
gocreate view v1
as
select khid,name,yszk,qtys=0,sk=0,jy=(select sum(yszk) from TA where t.khid=khid and t.name=name and t.yszk>=yszk)
from TA t
union all
select khid,name,yszk=0,qtys,sk=0,jy=(select sum(qtys) from TB where t.khid=khid and t.name=name and t.qtys>=qtys)+(select sum(yszk) from TA where t.khid=khid and t.name=name)
from TB t
union all
select khid,name,yszk=0,qtys=0,sk,jy=(select sum(-sk) from TC where t.khid=khid and t.name=name and t.sk>=sk)+(select sum(qtys) from TB where t.khid=khid and t.name=name)+(select sum(yszk) from TA where t.khid=khid and t.name=name)
from TC t
goselect * from v1drop table TA,TB,TC
/* 不知道这样的结果行不行khid name yszk qtys sk jy
----------- ---------- ----------- ----------- ----------- -----------
1 客户A 10000 0 0 10000
1 客户A 0 888 0 10888
1 客户A 0 8000 0 18888
1 客户A 0 9000 0 27888
1 客户A 0 0 899 26989
1 客户A 0 0 999 25990(所影响的行数为 6 行)*/
既然无法用一条语句产生这样的结果,那么又要生成视图,无非要先写一个批语句,能得到这样的结果.
将批语句改写为存储过程,使用
再使用
create view v
as
select * from openrowset(......) a 这样的语句来完成
as
select khid,name,yszk,qtys=0,sk=0,jy=(select sum(yszk) from TA where t.khid=khid and t.name=name and t.yszk>=yszk)
from TA t
union all
select khid,name,yszk=0,qtys,sk=0,jy=(select sum(qtys) from TB where t.khid=khid and t.name=name and t.qtys>=qtys)+(select sum(yszk) from TA where t.khid=khid and t.name=name)
from TB t
union all
select khid,name,yszk=0,qtys=0,sk,jy=(select sum(-sk) from TC where t.khid=khid and t.name=name and t.sk>=sk)+(select sum(qtys) from TB where t.khid=khid and t.name=name)+(select sum(yszk) from TA where t.khid=khid and t.name=name)
from TC t
go------------------------------------------
三列中有重复值时,不对了吧
当然,如果是以a,b,c这样做后辍的,当然也可以用union加排序得到结果, 而真实的数据是没有规则的, 行不通.
用视图写确实比较难。。create table ta (khid int,name varchar(10),yszk int)
insert into ta select 1,'客户A',10000create table tb (khid int,name varchar(10),qtys int)
insert into tb select 1,'客户A',8000
insert into tb select 1,'客户A',9000
insert into tb select 1,'客户A',888create table tc (khid int,name varchar(10),sk int)
insert into tc select 1,'客户A',899
insert into tc select 1,'客户A',999
select id=identity(int,1,1),* into #temp from
(select khid,name,yszk,0 qtys,0 sk,0 jy from ta
union all
select khid,name,0 yszk,qtys,0 sk,0 jy from tb
union all
select khid,name,0 yszk,0 qtys,sk,0 jy from tc)a
select a.khid,a.name,a.yszk,a.qtys,a.sk,
jy=(select sum(yszk)+sum(qtys)+sum(sk) from #temp where id<=a.id and a.name=name)
from #temp a
视图要增加排序列。
2005用row_number
只要不同的客户中每个表里有不重复的日期,就可以处理.
如果同一个客户同一个表的记录里有重复的日期,还是无法一条语句处理.
否则还得用存储过程 + openrowset或opendatasourcesql2005确实可以借助row_number实现