【紧急求助】如下sql 语句如何写，请进.......

[newchar]

例如：
T1(id,cardid)
T2(id,company,effdate)

数据：
T1:
aaa,1111
bbb,1111
......

T2:
aaa,company1,2010-01-01
aaa,company2,2010-10-01
bbb,company3,2010-12-01

想要结果：cardid相同取最后生效公司对应，即
aaa,1111,company3
bbb,1111,company3
......

如何写..........谢谢！

[AcHerat]

select a.id,a.cardid,b.company
from t1 a join t2 b on a.id = b.id
where not exists (select 1 from t2 where id = b.id and effdate > b.effdate)

[newchar]

[Quote=引用 1 楼 acherat 的回复:]
SQL code


select a.id,a.cardid,b.company
from t1 a join t2 b on a.id = b.id
where not exists (select 1 from t2 where id = b.id and effdate > b.effdate)
[/Quote]
aaa 1111 company2
bbb 1111 company3

结果与目标有点差异

[geniuswjt]

select a.id,a.cardid,b.company
from T1 a，T2 b where a.id = b.id
and not exists (select 1 from T2 where id = b.id and effdate > b.effdate)

[oO寒枫Oo]

想要结果
aaa到底对应的是3 还是2

[geniuswjt]

我倒。你这个需求里aaa最后生效公司是company2好吗？
所以答案没错。。。[Quote=引用 2 楼 newchar 的回复:]
引用 1 楼 acherat 的回复:
SQL code


select a.id,a.cardid,b.company
from t1 a join t2 b on a.id = b.id
where not exists (select 1 from t2 where id = b.id and effdate > b.effdate)

aaa 1111 company2
……
[/Quote]

[newchar]

[Quote=引用楼主 newchar 的回复:]
例如：
T1(id,cardid)
T2(id,company,effdate)

数据：
T1:
aaa,1111
bbb,1111
......

T2:
aaa,company1,2010-01-01
aaa,company2,2010-10-01
bbb,company3,2010-12-01

想要结果：cardid相同取最后生效公司对应，即
aaa,11……
[/Quote]

想要结果如下:
aaa 1111 company3
bbb 1111 company3

[oO寒枫Oo]

select a.*
(select a.id,cardid,company,effdate
from T1,T2 a
where T1.id=T2.id)aa  where not exists( select (select a.id,cardid,company,effdate
from T1,T2 a
where T1.id=T2.id)bb where aa.cardid=bb.cardid and aa.effdate<bb.effdate)

[oO寒枫Oo]

select a.* (select a.id,cardid,company,effdate from T1,T2 a where T1.id=T2.id)aa where not exists( select 1 from (select a.id,cardid,company,effdate from T1,T2 a where T1.id=T2.id)bb where aa.cardid=bb.cardid and aa.effdate<bb.effdate) 

[oO寒枫Oo]

看错了 楼主描述和结果是一致的 呵呵。7楼是错的

[geniuswjt]

select a.id,a.cardid,b.company
from T1 a，T2 b where a.id = b.id
and not exists (select 1 from T2 where cardid= b.cardid and effdate > b.effdate)

额，看错

[oO寒枫Oo]

select aa.* (select a.id,cardid,company,effdate from T1,T2 a where T1.id=T2.id)aa where not exists( select 1 from (select a.id,cardid,company,effdate from T1,T2 a where T1.id=T2.id)bb where aa.cardid=bb.cardid and aa.effdate<bb.effdate)

[AcHerat]

select a.id,a.cardid,b.company
from t1 a join t2 b on a.id = b.id
where not exists (select 1 from t2 where effdate > b.effdate)

--不过这个效率不高！

select a.id,a.cardid,b.company
from t1 a,
  (select company from t2 b where not exists (select 1 from t2 where effdate > b.effdate)) b

--or

select a.id,a.cardid,b.company
from t1 a,
  (select top 1 company from t2 order by effdate desc) b

[oO寒枫Oo]

select aa.* from (select a.id,cardid,company,effdate from T1,T2 a where T1.id=T2.id)aa where not exists( select 1 from (select a.id,cardid,company,effdate from T1,T2 a where T1.id=T2.id)bb where aa.cardid=bb.cardid and aa.effdate<bb.effdate)

[i_num]

select a.id,a.cardid,b.company from T1 a jion T2 b on a.id=b.id where not exists (select id form t2 where id=b.id and effdate>b.effdate)