表Income
Ino Iprice Idate
1002 2.60 2007-04-11
1002 2.54 2007-03-21
1002 2.80 2007-08-31
1003 3.00 2006-12-18
1003 4.50 2007-10-06
1005 15.00 2007-05-10
1006 31.50 2007-11-20
1006 33.00 2005-08-26
……
表 Item
Ino price
1001 0.00
1002 0.00
1003 0.00
1004 0.00
…… 更新表Item,使得表Item中price的数据是表Income最近日期的Iprice的数据
Ino Iprice Idate
1002 2.60 2007-04-11
1002 2.54 2007-03-21
1002 2.80 2007-08-31
1003 3.00 2006-12-18
1003 4.50 2007-10-06
1005 15.00 2007-05-10
1006 31.50 2007-11-20
1006 33.00 2005-08-26
……
表 Item
Ino price
1001 0.00
1002 0.00
1003 0.00
1004 0.00
…… 更新表Item,使得表Item中price的数据是表Income最近日期的Iprice的数据
解决方案 »
- SUM 函数无法使用
- 想学C++,求容易懂的教材
- 这种写法SQL 2000不支持?
- 高分win2000与win2003下的sql server 连接问题
- 急,DTS脚本问题
- 一个关于合并行记录的问题,谢谢大家
- 急!▇▇▇▇▇ SQL数据过滤问题。高手来,有主逻辑错误。在线等
- SQL Server链接服务器,因为 OLE DB 提供程序 'SQLOLEDB' 无法启动分布式事务?
- MS SQL server 2000 大家如果有做unicode的经验,请大家给点意见,谢谢!!
- 求一sql语句?解决立即结贴!!!
- 急,求如下视图!
- 菜鸟问题: 给MS SQL 2000 写的文件可以倒到MS SQL 2003 里吗?
create table Income(Ino varchar(5),Iprice numeric(13,2),Idate varchar(10))
create table Item(Ino varchar(5),price numeric(13,2))
insert into Income(Ino,Iprice,Idate)
select'1002', 2.60 ,'2007-04-11' union all
select'1002', 2.54 ,'2007-03-21' union all
select'1002', 2.80 ,'2007-08-31' union all
select'1003', 3.00 ,'2006-12-18' union all
select'1003', 4.50 ,'2007-10-06' union all
select'1005', 15.00 ,'2007-05-10' union all
select'1006', 31.50 ,'2007-11-20' union all
select'1006', 33.00 ,'2005-08-26'
insert into Item(Ino,price)
select'1001', 0.00 union all
select'1002' , 0.00 union all
select'1003' , .00 union all
select'1004 ' , 0.00
update Item set price=Iprice
from (
select Income.* from Income join (select Ino,max(Idate)Idate from Income group by Ino) as IncomeA
ON Income.Ino=IncomeA.Ino and Income.Idate=IncomeA.Idate ) as tmp
where tmp.Ino=Item.Ino
(所影响的行数为 2 行)
set price=b.Iprice
from
Item a
join
Income b on a.Ino=b.Ino
where
not exists(select 1 from Income where Ino=b.Ino and Idate>b.Idate)
set price=(select top 1 Iprice from Income b where Idate=(select max(Idate) from Income where Ino=b.Ino) and Ino=Item.Ino)
set price=
isnull(
(select top 1 Iprice from Income b where Idate=(select max(Idate) from Income where Ino=b.Ino) and Ino=Item.Ino)
,price)
--加isnull,如果Income表不存在时,不更新
set price = n.Iprice
from item m,
(
select a.* from income a,
(select ino,max(idate) idate from income group by ino) b
where a.ino = b.ino and a.idate = b.idate
) n
where m.ino = n.ino
update item set price=b.iprice from (select ino,iprice from income a where
not exists (select * from income where ino=a.ino and idate>a.idate))b where item.ino=b.ino
insert into C select 1002,2.60, '2007-04-11 '
insert into C select 1002,2.54, '2007-03-21 '
insert into C select 1002,2.80, '2007-08-31 '
insert into C select 1003,3.00, '2007-12-18 '
insert into C select 1003,4.50, '2007-10-06 '
insert into C select 1005,15.00, '2007-05-10 '
insert into C select 1006,31.00, '2007-11-20 '
insert into C select 1006,33.00, '2007-08-26 ' create table d (Ino int primary key,price decimal(5,2))
insert into d select 1002,0.00
insert into d select 1003,0.00
insert into d select 1005,0.00
insert into d select 1006,0.00
update d set price=(select iprice from c where c.ino=d.ino and idate =(select max(idate) from c where c.ino=d.ino))
set price=b.iprice
from item a,income b,
(select ino,max(idate) idate from income group by ino) c
where a.ino=b.ino and b.ino=c.ino and b.idate=c.idate