你写的视图好象不对,4月份DOLLAR, HK 分别有两种汇率
解决方案 »
- 各位,请帮帮忙,Drop了一张表,有什么办法能够恢复?或者有什么好的工具能够恢复?
- 请教一条SQL语句.在线!!!
- sql2005 SQL SERVER AGENT服务无法启动
- sql狂吃内存
- 邹建大哥,帮忙呀!求查询sql语句
- SQL2005 导出数据到EXCEl表时出错
- SQL 中合并多行数据
- 关于SQLSERVER 的全面问题 一:日志!!!
- SQL2000发布和订阅的问题
- sql 查询语句中,怎么引用input输入的文字,
- 第三次提问:在本地telnet 127.0.0.1 1433 报错:在端口1433连接失败,如何解决? 系统2003,sql是2000标准版,端口是1433,没有启用防火墙.
- 如何对字段中的内容进行如下处理?
create table ta (start_date smalldatetime, end_date smalldatetime, currency_code varchar(20), rate numeric (18, 1))insert into ta
select '2005-01-01', '2005-04-16', 'HK', 1.1 union all
select '2005-04-17', '2005-05-08', 'HK', 1.2 union all
select '2005-01-01', '2005-04-20', 'DOLLAR', 8.1 union all
select '2005-04-21', '2005-05-31', 'DOLLAR' , 8.2
create table tb (year_y int, month_m int, start_date smalldatetime, end_date smalldatetime)
insert into tb
select 2005, 1, '2005-01-01', '2005-01-31' union all
select 2005, 2, '2005-02-01' , '2005-02-28' union all
select 2005, 3, '2005-03-01' , '2005-03-31' union all
select 2005, 4, '2005-04-01' , '2005-04-30' union all
select 2005, 5, '2005-05-01' , '2005-05-31'--answer:
select year_y, month_m, tb.start_date, tb.end_date, currency_code, rate from tb left join ta on month_m >= month(ta.start_date) and
year_y = year(ta.start_date) and month_m <= month(ta.end_date) and year_y = year(ta.end_date)----
我得到的结果是12行,4月份的汇率DOLLAR, HK都是2个
每个月每一币种的汇率只有一个,以该月的end_date时的汇率为准
insert into @a
select '2005-01-01','2005-04-16','HK',1.1 union all
select '2005-04-17','2005-05-08','HK',1.2 union all
select '2005-01-01','2005-04-20','DOLLAR',8.1 union all
select '2005-04-21','2005-05-31','DOLLAR',8.2declare @b table(year_y int,month_m int,start_date datetime,end_date datetime)
insert into @b
select 2005,1,'2005-01-01','2005-01-31' union all
select 2005,2,'2005-02-01','2005-02-28' union all
select 2005,3,'2005-03-01','2005-03-31' union all
select 2005,4,'2005-04-01','2005-04-30' union all
select 2005,5,'2005-05-01','2005-05-31'
/*
得到如下视图:
//在表b中求每期间(year_y,month_m)的结束日(end_date)所对应的各种币种的汇率year_y month_m start_date end_date currency_code rate
2005 1 2005-01-01 2005-01-31 HK 1.1
2005 1 2005-01-01 2005-01-31 DOLLAR 8.1
2005 2 2005-02-01 2005-02-28 HK 1.1
2005 2 2005-02-01 2005-02-28 DOLLAR 8.1
2005 3 2005-03-01 2005-03-31 HK 1.1
2005 3 2005-03-01 2005-03-31 DOLLAR 8.1
2005 4 2005-04-01 2005-04-30 HK 1.2
2005 4 2005-04-01 2005-04-30 DOLLAR 8.2
2005 5 2005-05-01 2005-05-31 HK 1.2
2005 5 2005-05-01 2005-05-31 DOLLAR 8.2
*/select b.*,a.currency_code,a.rate
from @b b
left join (select currency_code,rate,case when day(start_date)>1 then convert(char(7),start_date,120)+'-01' else start_date end as start_date,
case when exists(select 1 from @a where currency_code=t.currency_code and end_date>t.end_date) then dateadd(d,-1,convert(char(7),end_date,120)+'-01')
else dateadd(d,-1,convert(char(7),dateadd(m,1,end_date),120)+'-01') end as end_date from @a t)a
on b.start_date between a.start_date and a.end_date
year_y month_m start_date end_date currency_code rate
在数据库重创建新视图:
create view dbo.viewRate
as
select dbo.b.*,dbo.a.currency_code,dbo.a.rate from dbo.b left join dbo.a on dbo.b.end_date>=dbo.a.start_date and dbo.b.end_date<=dbo.a.end_date and dbo.b.year_y=year(dbo.a.end_date) and dbo.b.month_m=month(dbo.a.end_date)不过在视图设计中不知道能否用函数year和month
AS
SELECT dbo.b.*, dbo.a.currency_code AS 币种, dbo.a.rate AS 汇率
FROM dbo.b LEFT OUTER JOIN
dbo.a ON dbo.b.end_date >= dbo.a.start_date AND
dbo.b.end_date <= dbo.a.end_date AND dbo.b.year_y = YEAR(dbo.a.end_date)这样你试试!!!