这个查询怎么写？

[wangxiao2008]

两个表：T1 结构如下：

number id
aaa 1
bbb 2
ccc 1
T2结构如下：
id date num
1 20010101 aaa
1 20020305 aa1
1 null ccc
2 20000506 ww
2 20030306 43as
2 20000608 454
需要查询T2的num
查询条件：1、两个表id相同，2、T2的num在T1的number里面，3、前两个查询一个id有多条记录时取日期最小的那个num值。
怎么写好呢？

[SQLCenter]

就这些数据，需要的结果是什么

[xiaoxiangqing]

select * from t2 a join t1 b on a.id=b.id and a.num=b.number
where not exists (select 1 from t2 b where a.id=b.id and a.num=b.num and a.date>b.date)

[wangxiao2008]

需要T2的num就行
比如id=1的，我只要ccc就可以了，因为aaa，ccc都同时满足1、2
但是由于ccc的日期是空比aaa的日期小

[SQLCenter]

那改改2楼的，你看对不对：

select * from t2 a join t1 b on a.id=b.id and a.num=b.number
where not exists (select 1 from t2 b where a.id=b.id and isnull(a.date,0)>isnull(b.date,0))

2、T2的num在T1的number里面
---
我主要是拿不准这什么意思，象2楼那样 a.num=b.number ？

[wangxiao2008]

2楼的不对

[SQLCenter]

select * from t2 a where exists (select 1 from t1 where id=a.id)
and exists (select 1 from t1 where number=a.num)
and not exists (select 1 from t2 where id=a.id and isnull(date,0)<isnull(a.date,0))

[「已注销」]

SELECT DISTINCT m.id,m.date,m.num FROM #tb t
LEFT JOIN
(
	SELECT * FROM #tc t 
	WHERE NOT EXISTS(SELECT NULL FROM #tc 
	                 WHERE id=t.id AND isnull(date,'0')<isnull(t.date,'0'))
)m
ON t.id=m.id 
--AND t.number=m.num   ----这两个条件任意一个应该都是可以的
AND exists (select NULL from #tb where number=m.num) ----
WHERE m.id IS NOT NULL

id          date       num
----------- ---------- ----------
1           NULL       ccc

(1 row(s) affected)

[ForFumm]

select * from  t1 a,t2 b where a.id=b.id 
 and not exists  (select 1 from t2 where   id=b.id and datediff(dd,b.[date],[date]) <0  )  and  b.num in ( select number from t1 )
number       id          id          date       num
------------ ----------- ----------- ---------- ------------
aaa          1           1                      ccc
ccc          1           1                      ccc

[brownhwy]

Select T.ID,T.[Date],T2.Num From
(SELECT dbo.T1.ID, MIN(dbo.T2.[Date]) AS [Date]
FROM dbo.T1 INNER JOIN
      dbo.T2 ON dbo.T1.ID = dbo.T2.ID
GROUP BY dbo.T1.ID) T Inner join T2 On T.ID=T2.ID And T.[Date]=T2.[Date]
Where Exists (Select 1 From T1 Where T2.Num=T1.Number)

[dawugui]

create table T1(number varchar(10),id int)
insert into t1 values('aaa' ,1)
insert into t1 values('bbb' ,2)
insert into t1 values('ccc' ,1)
create table T2(id int,date datetime,num varchar(10))
insert into t2 values(1 ,'20010101', 'aaa')
insert into t2 values(1 ,'20020305', 'aa1')
insert into t2 values(1 ,null ,      'ccc')
insert into t2 values(2 ,'20000506', 'ww')
insert into t2 values(2 ,'20030306', '43as')
insert into t2 values(2 ,'20000608', '454')
go

select t1.* , m.* from t1 , t2 m
where t1.id = m.id and charindex(m.num,t1.number) > 0
and m.date = (select min(date) from t2 where t2.id = m.id)

drop table t1 , t2

/*
number     id          id          date                                                   num        
---------- ----------- ----------- ------------------------------------------------------ ---------- 
aaa        1           1           2001-01-01 00:00:00.000                                aaa

（所影响的行数为 1 行）
*/