求一条SQL语句

[ginpq]

表1
code num
101 A01
102 A01
101 A02
103 A02

表2
time num
2010-07-05 13:51:29 A01
2010-07-01 17:28:16 A02

注：表1和表2的num是关联的；

现需select 表1.code
要求：
1.不能重复；
2.以表2的time排序。

这个一条SQL语句能够实现吗？

[andy_liucj]

if object_id('tb') is not null drop table tb
create table tb(code int,num char(4))

insert tb
select 101,'A01' UNION ALL
select 102,'A01' UNION ALL
select 101,'A02' union all
select 103,'A02'

if object_id('tc') is not null drop table tc
create table tc([time] datetime,num char(4))

insert tc
select '2010-07-05 13:51:29','A01' UNION ALL
SELECT '2010-07-01 17:28:16','A02'


select code from (select a.code,max(b.[time]) 'time' from tb a join tc b on a.num=b.num group by a.code) t order by t.[time]

--是max，还是min你自己选吧

--这是max的结果
code
-----------
103
101
102

(3 row(s) affected)

[shixixi1987]

if object_id('[tb1]') is not null drop table [tb1]
create table [tb1]([code] varchar(10),[num] varchar(10))
insert [tb1]
select '101','A01' union all
select '102','A01' union all
select '101','A02' union all
select '103','A02'
go
if object_id('[tb2]') is not null drop table [tb2]
create table [tb2]([time] datetime,[num] varchar(10))
insert [tb2]([time],[num])
values('2010-07-05 13:51:29.920','A01')
insert [tb2]([time],[num])
select '2010-07-01 17:28:16.920','A02'
go
select tb1.num,tb2.time from tb2,tb1 where tb2.num=tb1.num 
 group by tb1.num,tb2.time order by tb2.time desc
go

num        time
---------- -----------------------
A01        2010-07-05 13:51:29.920
A02        2010-07-01 17:28:16.920

[ginpq]

测试完，#11楼 不完全对，#12楼 对了，#13楼 是要code。

[华夏小卒]

select distinct code from ta a,tb b where a.num=b.num order by time

先猜吧

[ginpq]

这样肯定不行

[永生天地]

我也猜一个

select distinct a.code
from 表1 a left join 表2 b
on a.num=b.num
where not exists(select 1 from 表2 where num=a.num and time >b.time )
order by b.time

[xyj052]

LZ说的问题不够详细，说清楚点，不然只能猜了

[ginpq]

“如果指定了select distinct ，则order by 项必须出现在选择列表中。”
亲测，如果添加后则无法过滤重复。

[情殇无限]

select code 
from tabA
order by (select top 1 time  from tabB where num=tabA.num order by time)

[ginpq]

select distinct a.code
from 表1 a left join 表2 b
on a.num=b.num
where not exists(select 1 from 表2 where num=a.num and time >b.time )

这是正确的；

select distinct a.code,b.time
from 表1 a left join 表2 b
on a.num=b.num
where not exists(select 1 from 表2 where num=a.num and time >b.time )
order by b.time

无法过滤重复，如果把 select 里的b.time去掉语句不正确。

[ginpq]

补充一点：我用的是SQL CE无法使用TOP

[情殇无限]

select code 
from tabA
order by (select time from tabB x where num=tabA.num 
and not exists(select 1 from tabB  where num=x.num and time >x.time )
)

[hokor]

[Quote=引用 8 楼 ginpq 的回复:]
补充一点：我用的是SQL CE无法使用TOP
[/Quote]
我就想知道一下去重的时候保留哪一条？
101,A01
101,A02
你要哪个？101对应两个时间，你不说要哪个？我们怎么知道101 该拍哪里啊？

[hokor]

select a.code from (
select a.code,max(b.time) time from 
from 表1 a join 表2 b on a.num = b.num) t
order by time


select a.code from (
select a.code,min(b.time) time from 
from 表1 a join 表2 b on a.num = b.num) t
order by time