22,209
社区成员
发帖
与我相关
我的任务
分享
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2014-03-21 12:00:26
-- Verstion:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([id] int,[name] varchar(3))
insert [A]
select 1,'m-1' union all
select 2,'m-2' union all
select 3,'m-3'
--> 测试数据:[b]
if object_id('[b]') is not null drop table [b]
go
create table [b]([eid] int,[price] int,[addtime] datetime)
insert [b]
select 1,30,'2014-1-1' union all
select 1,20,'2014-1-2' union all
select 1,79,'2014-1-3' union all
select 2,100,'2014-1-6'
--------------开始查询--------------------------
select
a.id,a.name,b.price
from
a inner join b on a.id=b.eid
where
not exists(select 1 from b as t where eid=b.eid and addtime>b.addtime)
----------------结果----------------------------
/* id name price
----------- ---- -----------
1 m-1 79
2 m-2 100
(2 行受影响)
*/
select
a.id,a.name,b.price
from
a inner join b on a.id=b.eid
where
not exists(select 1 from b as t where eid=b.eid and addtime<b.addtime)