有如下两个表
create table #a (bizdate varchar(8), fundid varchar(8), tradvolume money )
insert into #a select '20081101', '11000001', 25345.00
insert into #a select '20081101', '11000002', 213543.00
insert into #a select '20081101', '11000003', 11453345.00
insert into #a select '20081101', '11000004', 22155345.00
insert into #a select '20081101', '11000005', 25242345.00 insert into #a select '20081102', '11000001', 123156.12
insert into #a select '20081102', '11000002', 23153131.12
insert into #a select '20081102', '11000003', 5311313.52
insert into #a select '20081102', '11000004', 5131831.02
insert into #a select '20081102', '11000005', 51318623.52 insert into #a select '20081103', '11000001', 513198.153
insert into #a select '20081103', '11000002', 424531.01
insert into #a select '20081103', '11000003', 423486.15
insert into #a select '20081103', '11000004', 54315.15
insert into #a select '20081103', '11000005', 541315.51 insert into #a select '20081104', '11000001', 51315.15
insert into #a select '20081104', '11000002', 51351531.15
insert into #a select '20081104', '11000003', 81315335.50
insert into #a select '20081104', '11000004', 5138.15
insert into #a select '20081104', '11000005', 813153.51 create table #b (bizdate varchar(8), fundid varchar(8), asset money )insert into #b select '20081101', '11000001', 42725345.00
insert into #b select '20081101', '11000002', 2135424543.00
insert into #b select '20081101', '11000003', 11442453345.00
insert into #b select '20081101', '11000004', 22145755345.00
insert into #b select '20081101', '11000005', 25445242345.00 insert into #b select '20081102', '11000001', 12384523156.12
insert into #b select '20081102', '11000002', 2314527853131.12
insert into #b select '20081102', '11000003', 5724311313.52
insert into #b select '20081102', '11000004', 7285131831.02
insert into #b select '20081102', '11000005', 4851318623.52 insert into #b select '20081103', '11000001', 5134527198.153
insert into #b select '20081103', '11000002', 75424531.01
insert into #b select '20081103', '11000003', 7278423486.15
insert into #b select '20081103', '11000004', 54548315.15
insert into #b select '20081103', '11000005', 548741315.51 insert into #b select '20081104', '11000001', 7455851315.15
insert into #b select '20081104', '11000002', 5714351531.15
insert into #b select '20081104', '11000003', 8421315335.50
insert into #b select '20081104', '11000004', 51428738.15
insert into #b select '20081104', '11000005', 81473153.51 #a是交易量, #b是资产总值。 我想实现这样一个目的,当统计任意时间段内的客户的交易量时,将资产总值表中日期与统计时段结束时间那一天客户的资产和#a关联起来,并且是以视图的形式实现。
假如视图名为xxx,则通过调用 select * from xxx where bizdate between '20081102' and '20081104' 得到如下结果
fundid tradvolume asset
11000001 687669.42 7455851315.15
11000002 74929193.28 5714351531.15
11000003 87050135.17 8421315335.50
11000004 5191284.32 51428738.15
11000005 52673092.54 81473153.51
解决方案 »
- 修短字符串字段,造成将截断字符串或二进制数据?
- 非齐整数据表的建立
- 求一语句能够分类计数
- 星期一做为一周的第1天 SELECT DATEADD(Day,@number-(DATEPART(Weekday,@dt)+@@DATEFIRST-
- 求编码格式,及解码
- 请问用jdbc如何取得带compute字句的结果集?
- 高手们,请问这样的问题你能回答吗?
- 请问各位:在sql server7.0中如何用sql语句更改表结构?谢谢!!!!
- 记得有个C高手说过,实现的过程。 可以用于任何语言(VC、BCB、DELPHI...)。现在记不到了,如果可以用存储过程写那一定是最好的。
- 有关SQL SERVER复制的问题
- msde 2000 数据库备份加密有问题,请高手看看!
- 问一个日期型转换问题。
楼主就要这一句的呀select * from xxx where bizdate between '20081102' and '20081104'
go
create table ta (bizdate varchar(8), fundid varchar(8), tradvolume money )
insert into ta select '20081101', '11000001', 25345.00
insert into ta select '20081101', '11000002', 213543.00
insert into ta select '20081101', '11000003', 11453345.00
insert into ta select '20081101', '11000004', 22155345.00
insert into ta select '20081101', '11000005', 25242345.00 insert into ta select '20081102', '11000001', 123156.12
insert into ta select '20081102', '11000002', 23153131.12
insert into ta select '20081102', '11000003', 5311313.52
insert into ta select '20081102', '11000004', 5131831.02
insert into ta select '20081102', '11000005', 51318623.52 insert into ta select '20081103', '11000001', 513198.153
insert into ta select '20081103', '11000002', 424531.01
insert into ta select '20081103', '11000003', 423486.15
insert into ta select '20081103', '11000004', 54315.15
insert into ta select '20081103', '11000005', 541315.51 insert into ta select '20081104', '11000001', 51315.15
insert into ta select '20081104', '11000002', 51351531.15
insert into ta select '20081104', '11000003', 81315335.50
insert into ta select '20081104', '11000004', 5138.15
insert into ta select '20081104', '11000005', 813153.51 create table tb (bizdate varchar(8), fundid varchar(8), asset money )insert into tb select '20081101', '11000001', 42725345.00
insert into tb select '20081101', '11000002', 2135424543.00
insert into tb select '20081101', '11000003', 11442453345.00
insert into tb select '20081101', '11000004', 22145755345.00
insert into tb select '20081101', '11000005', 25445242345.00 insert into tb select '20081102', '11000001', 12384523156.12
insert into tb select '20081102', '11000002', 2314527853131.12
insert into tb select '20081102', '11000003', 5724311313.52
insert into tb select '20081102', '11000004', 7285131831.02
insert into tb select '20081102', '11000005', 4851318623.52 insert into tb select '20081103', '11000001', 5134527198.153
insert into tb select '20081103', '11000002', 75424531.01
insert into tb select '20081103', '11000003', 7278423486.15
insert into tb select '20081103', '11000004', 54548315.15
insert into tb select '20081103', '11000005', 548741315.51 insert into tb select '20081104', '11000001', 7455851315.15
insert into tb select '20081104', '11000002', 5714351531.15
insert into tb select '20081104', '11000003', 8421315335.50
insert into tb select '20081104', '11000004', 51428738.15
insert into tb select '20081104', '11000005', 81473153.51
gocreate function f_test (@s varchar(10),@e varchar(10))
returns @t table(fundid varchar(8),tradvolume money,asset money)
as
begin
insert into @t
select isnull(a.fundid,b.fundid) as fundid,tradvolume,asset
from (
select fundid , asset from tb where bizdate = @e) a
full join(
select fundid,sum(tradvolume) as tradvolume from ta where bizdate between @s and @e group by fundid) b
on a.fundid = b.fundid
return
end
goselect * from dbo.f_test('20081102' ,'20081104' )
/*
fundid tradvolume asset
-------- --------------------- ---------------------
11000001 687669.423 7455851315.15
11000002 74929193.28 5714351531.15
11000003 87050135.17 8421315335.50
11000004 5191284.32 51428738.15
11000005 52673092.54 81473153.51
*/
drop table ta,tb
drop function f_test
set nocount off