导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

求一SQL

helphelpmeme 2007-12-20 09:43:16
A表中有字段id,compDate
1 2006-12-01
1 2007-01-02
2 2007-01-03
2 2007-05-05
B表中有 id,effiDate price
1 2006-05-09 20
1 2007-01-01 10
2 2007-01-01 15

B表中的内容是每次更改后的最新价格,日期.A表为出商品的出售日期.
如何得到A表中全部的id信息,compDate,及在B表中effiDate以内的price
想要的结果为
id totalprice
1 30
2 30
...全文
32 点赞 收藏 2
写回复
2 条回复
切换为时间正序
请发表友善的回复…
发表回复
leo_lesley 2007-12-20
create table A(id int,compDate datetime)
insert A
select 1 ,'2006-12-01'
union select 1 ,'2007-01-02'
union select 2 ,'2007-01-03'
union select 2 ,'2007-05-05'
go
create table B(id int,effiDate datetime,price int)
insert B
select 1 ,'2006-05-09', 20
union select 1 ,'2007-01-01', 10
union select 2 ,'2007-01-01', 15
go

select id,totalprice=sum(totalprice)
from (
select id,compDate,
totalprice=(select top 1 price from B where A.id=id and A.compDate>effiDate order by effiDate desc)
from A ) t
group by id

go
drop table A,B

/* 结果
id totalprice
----------- -----------
1 30
2 30

(所影响的行数为 2 行)
*/
回复
tim_spac 2007-12-20

/*
A表中有字段id,compDate
1 2006-12-01
1 2007-01-02
2 2007-01-03
2 2007-05-05
B表中有 id,effiDate price
1 2006-05-09 20
1 2007-01-01 10
2 2007-01-01 15

B表中的内容是每次更改后的最新价格,日期.A表为出商品的出售日期.
*/
set nocount on
go
create table table_A (
id int,
compDate datetime
)
insert into table_A
select 1,'2006-12-01' union
select 1,'2007-01-02' union
select 2,'2007-01-03' union
select 2,'2007-05-05'

create table table_B (
id int,
effiDate datetime,
price numeric(10,2)
)
insert into table_B
select 1,'2006-05-09',20 union
select 1,'2007-01-01',10 union
select 2,'2007-01-01',15
go
/*
如何得到A表中全部的id信息,compDate,及在B表中effiDate以内的price
想要的结果为
id totalprice
1 30
2 30
*/
select id, totalPrice = sum(price)
from (
select id, price = (select top 1 price from table_B b where a.id=b.id and effiDate<compDate order by effiDate desc)
from table_A a) as a
group by id
go
drop table table_A
drop table table_B
go
回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告