有两个表:table1(订单) ,table2(询价表)table1 字段:id,part,date
table2 字段:id,part,reldate,price
例如:a mm1 2011-6-7
b mm2 2011-6-10
c mm3 2011-6-25
id part reldate price
a mm1 2011-6-6 1.2
b mm2 2011-6-10 1.4
c mm3 2011-6-22 1.3查询出 订单表中,对应料号mm1,mm2,mm3的价格。
想得到的查询结果:
a mm1 2011-6-7 1.2
b mm2 2011-6-10 1.4
c mm3 2011-6-25 1.3
2011-6-7 下单肯定获取的单价是6-6询价
2011-6-10 下单肯定获取的单价是6-10询价
2011-6-25 下单肯定获取的单价是2011-6-22最新询价 以上是举例,实际上有订单记录表和比价表有大量数据
table2 字段:id,part,reldate,price
例如:a mm1 2011-6-7
b mm2 2011-6-10
c mm3 2011-6-25
id part reldate price
a mm1 2011-6-6 1.2
b mm2 2011-6-10 1.4
c mm3 2011-6-22 1.3查询出 订单表中,对应料号mm1,mm2,mm3的价格。
想得到的查询结果:
a mm1 2011-6-7 1.2
b mm2 2011-6-10 1.4
c mm3 2011-6-25 1.3
2011-6-7 下单肯定获取的单价是6-6询价
2011-6-10 下单肯定获取的单价是6-10询价
2011-6-25 下单肯定获取的单价是2011-6-22最新询价 以上是举例,实际上有订单记录表和比价表有大量数据
你那个日期有什么用?这儿仅能按料号连接查询.
select a.part,b.price from t1 a inner join t2 b on a.part=b.part
select
id,part,max( reldate),max(price)
from
(
select *,0 from table1
union all
select * from table2
)t
group by
id,part
--> 测试数据: [table1]
if object_id('[table1]') is not null drop table [table1]
create table [table1] (id varchar(1),part varchar(3),date varchar(10))
insert into [table1]
select 'a','mm1','2011-6-7' union all
select 'b','mm2','2011-6-10' union all
select 'c','mm3','2011-6-25'
--> 测试数据: [table2]
if object_id('[table2]') is not null drop table [table2]
create table [table2] (id varchar(1),part varchar(3),reldate varchar(10),price numeric(2,1))
insert into [table2]
select 'a','mm1','2011-6-6',1.2 union all
select 'b','mm2','2011-6-10',1.4 union all
select 'c','mm3','2011-6-22',1.3--开始查询
select *,price=(select top 1 price from [table2] where part=a.part order by reldate desc)
from [table1] a--结束查询
drop table [table1],[table2]/*
id part date price
---- ---- ---------- ---------------------------------------
a mm1 2011-6-7 1.2
b mm2 2011-6-10 1.4
c mm3 2011-6-25 1.3(3 行受影响)
比价表中一个料号有多条的时候a mm1 2011-6-7
b mm2 2011-6-10
c mm3 2011-6-25
id part reldate price
a mm1 2011-6-5 1.2
a mm1 2011-6-16 1.6
b mm2 2011-6-10 1.4
c mm3 2011-6-22 1.3
其实料号是相同的,a mm1 2011-6-7
b mm1 2011-6-10
c mm1 2011-6-25
id part reldate price
a mm1 2011-6-5 1.2
a mm1 2011-6-16 1.6
b mm1 2011-6-10 1.4
c mm1 2011-6-22 1.3最后结果:
a mm1 2011-6-7 1.2
b mm2 2011-6-10 1.4
c mm3 2011-6-25 1.3
--这样,加个判断,之前我忘了写,嘿嘿
select *,
price=(select top 1 price from [table2] where part=a.part and reldate<=[date] order by reldate desc)
from [table1] a
--可以结贴了,如下:--> 测试数据: [table1]
if object_id('[table1]') is not null drop table [table1]
create table [table1] (id varchar(1),part varchar(3),date datetime)
insert into [table1]
select 'a','mm1','2011-6-7' union all
select 'b','mm1','2011-6-10' union all
select 'c','mm1','2011-6-25'
--> 测试数据: [table2]
if object_id('[table2]') is not null drop table [table2]
create table [table2] (id varchar(1),part varchar(3),reldate datetime,price numeric(2,1))
insert into [table2]
select 'a','mm1','2011-6-5',1.2 union all
select 'a','mm1','2011-6-16',1.6 union all
select 'b','mm1','2011-6-10',1.4 union all
select 'c','mm1','2011-6-22',1.3--开始查询
select *,
price=(select top 1 price from [table2] where part=a.part and reldate<=a.[date] order by reldate desc)
from [table1] a--结束查询
drop table [table1],[table2]/*
id part date price
---- ---- ----------------------- ---------------------------------------
a mm1 2011-06-07 00:00:00.000 1.2
b mm1 2011-06-10 00:00:00.000 1.4
c mm1 2011-06-25 00:00:00.000 1.3(3 行受影响)