请教一个sql语句如何写

Question

表table1的字段数据如下：
id    bs_id    price    date
1       1       20      2010-11-02
2       1       21      2010-11-04
3       1       23      2010-11-05
4       1       25      2010-11-08
5       2       30      2010-11-03
6       3       40      2010-11-04
7       3       41      2010-11-07
表table2的字段数据如下：
id    bs_id    price
1      1
2      2
3      3
我想实现，从table1里取出按date从大到小排序时，取第二个price，写入tabel2.price里去，如表table2的字段数据如下：
id    bs_id    price
1      1        23  (根据table1.bs_id=table2.bs_id，按table1.date顺序取2010-11-05的price)
2      2        30  (根据table1.bs_id=table2.bs_id，只有一行数据，则取当前一行的price)
3      3        40  (根据table1.bs_id=table2.bs_id，按table1.date顺序取2010-11-04的price)请问如何用sql语句来实现？

Answer 1

--sql 2000
update table2
set price = n.price
from table2 m, 
(select t.* , px = (select count(1) from table1 where bs_id = t.bs_id and date > t.date) + 1 from table1 t ) n
where m.bs_id = n.bs_id and px = 2 
--sql 2005
update table2
set price = n.price
from table2 m, 
(select t.* , px = row_number() over(partition by bs_id order by date desc) from table1 t ) n
where m.bs_id = n.bs_id and px = 2

Answer 2

我一楼回复有错.
2000如下:
create table table1(id int,bs_id int,price int,date datetime)
insert into table1 values(1, 1, 20 ,'2010-11-02')
insert into table1 values(2, 1, 21 ,'2010-11-04')
insert into table1 values(3, 1, 23 ,'2010-11-05')
insert into table1 values(4, 1, 25 ,'2010-11-08')
insert into table1 values(5, 2, 30 ,'2010-11-03')
insert into table1 values(6, 3, 40 ,'2010-11-04')
insert into table1 values(7, 3, 41 ,'2010-11-07')
create table table2(id int,bs_id int,price int)
insert into table2 values(1, 1,null)
insert into table2 values(2, 2,null)
insert into table2 values(3, 3,null)
goupdate table2
set price = (select top 1 price from (select t.* , px = (select count(1) from table1 where bs_id = t.bs_id and date > t.date) + 1 from table1 t ) n where px <= 2 and n.bs_id = m.bs_id)
from table2 m
select * from table2
/*
id          bs_id       price       
----------- ----------- ----------- 
1           1           23
2           2           30
3           3           40（所影响的行数为 3 行）*/drop table table1 , table2

Answer 3

--sql 2000
create table table1(id int,bs_id int,price int,date datetime)
insert into table1 values(1, 1, 20 ,'2010-11-02')
insert into table1 values(2, 1, 21 ,'2010-11-04')
insert into table1 values(3, 1, 23 ,'2010-11-05')
insert into table1 values(4, 1, 25 ,'2010-11-08')
insert into table1 values(5, 2, 30 ,'2010-11-03')
insert into table1 values(6, 3, 40 ,'2010-11-04')
insert into table1 values(7, 3, 41 ,'2010-11-07')
create table table2(id int,bs_id int,price int)
insert into table2 values(1, 1,null)
insert into table2 values(2, 2,null)
insert into table2 values(3, 3,null)
goupdate table2
set price = (select top 1 price from (select t.* , px = (select count(1) from table1 where bs_id = t.bs_id and date > t.date) + 1 from table1 t ) n where px <= 2 and n.bs_id = m.bs_id order by px desc)
from table2 m
select * from table2
/*
id          bs_id       price       
----------- ----------- ----------- 
1           1           23
2           2           30
3           3           40（所影响的行数为 3 行）*/drop table table1 , table2--sql 2005
create table table1(id int,bs_id int,price int,date datetime)
insert into table1 values(1, 1, 20 ,'2010-11-02')
insert into table1 values(2, 1, 21 ,'2010-11-04')
insert into table1 values(3, 1, 23 ,'2010-11-05')
insert into table1 values(4, 1, 25 ,'2010-11-08')
insert into table1 values(5, 2, 30 ,'2010-11-03')
insert into table1 values(6, 3, 40 ,'2010-11-04')
insert into table1 values(7, 3, 41 ,'2010-11-07')
create table table2(id int,bs_id int,price int)
insert into table2 values(1, 1,null)
insert into table2 values(2, 2,null)
insert into table2 values(3, 3,null)
goupdate table2
set price = (select top 1 price from (select t.* , px = row_number() over(partition by bs_id order by date desc) from table1 t ) n where px <= 2 and n.bs_id = m.bs_id order by px desc)
from table2 m
select * from table2
/*
id          bs_id       price
----------- ----------- -----------
1           1           23
2           2           30
3           3           40(3 行受影响)*/drop table table1 , table2

Answer 4

--借花献佛create table table1(id int,bs_id int,price int,date datetime)
insert into table1 values(1, 1, 20 ,'2010-11-02')
insert into table1 values(2, 1, 21 ,'2010-11-04')
insert into table1 values(3, 1, 23 ,'2010-11-05')
insert into table1 values(4, 1, 25 ,'2010-11-08')
insert into table1 values(5, 2, 30 ,'2010-11-03')
insert into table1 values(6, 3, 40 ,'2010-11-04')
insert into table1 values(7, 3, 41 ,'2010-11-07')create table table2(id int,bs_id int,price int)
insert into table2 values(1, 1,null)
insert into table2 values(2, 2,null)
insert into table2 values(3, 3,null)
go
update table2 set  price=(select top 1 table1.price from table1 inner join
(select bs_id,max(date) maxdate from table1 group by bs_id having count(date)=1
union all
select bs_id,max(date) from table1 where date<(select max(date) from table1 tb where table1.bs_id=tb.bs_id) group by bs_id --这里取小于最大值中的最大值,即第二大值
) tb
on table1.bs_id=tb.bs_id and table1.date=tb.maxdate
where table1.bs_id=M.bs_id)
from table2 Mselect * from table2/*
id          bs_id       price
----------- ----------- -----------
1           1           23
2           2           30
3           3           40(3 行受影响)*/

Answer 5

--借花献佛
create table table1(id int,bs_id int,price int,date datetime)
insert into table1 values(1, 1, 20 ,'2010-11-02')
insert into table1 values(2, 1, 21 ,'2010-11-04')
insert into table1 values(3, 1, 23 ,'2010-11-05')
insert into table1 values(4, 1, 25 ,'2010-11-08')
insert into table1 values(5, 2, 30 ,'2010-11-03')
insert into table1 values(6, 3, 40 ,'2010-11-04')
insert into table1 values(7, 3, 41 ,'2010-11-07')create table table2(id int,bs_id int,price int)
insert into table2 values(1, 1,null)
insert into table2 values(2, 2,null)
insert into table2 values(3, 3,null)
go
update table2 set  price=(select top 1 table1.price from table1 inner join
(select bs_id,max(date) maxdate from table1 group by bs_id having count(date)=1
union all
select bs_id,max(date) from table1 where date<(select max(date) from table1 tb where table1.bs_id=tb.bs_id) group by bs_id --这里取小于最大值中所有值的最大值,即第二大值
) tb
on table1.bs_id=tb.bs_id and table1.date=tb.maxdate
where table1.bs_id=M.bs_id)
from table2 Mselect * from table2
/*
id          bs_id       price
----------- ----------- -----------
1           1           23
2           2           30
3           3           40(3 行受影响)*/