select * from (
select * from fzrll tem where hzrq=(select max(hzrq) from fzrll where nbxh=tem.nbxh)) a,qydj b where a.nbxh=b.nbxh
select * from fzrll tem where hzrq=(select max(hzrq) from fzrll where nbxh=tem.nbxh)) a,qydj b where a.nbxh=b.nbxh
恭喜恭喜。我试了一下,还是都选出来了。请再修改一下吧?谢谢啦。
3715001000000002 赵之路 372526550908071 2003-05-19 00:00:00.000 QYDJ.*(略) 赵之路 372526550908071 37150010000000023715001000000002 赵之路 372526550908071 2003-05-19 00:00:00.000 QYDJ.* 王义朋 NULL 3715001000000002
where a.hzrq=(select min(hzrq) from fzrll where nbxh=a.nbxh)
select a.*,b.* from fzrll a ,qydj b where a.nbxh=b.nbxh
and a.hzrq=(select min(hzrq) from fzrll where nbxh=a.nbxh)
Create View v_fzrll as
select distinct fzrll.nbxh,fzrll.xm,fzrll.zjhm from fzrll,qydj b where fzrll.nbxh=b.nbxh and fzrll.hzrq=(select max(hzrq) from fzrll where fzrll.nbxh=b.nbxh) 然后:
select a.* ,b.* from v_fzrll a,qydj b where a.nbxh=b.nbxh即可。
declare @FZRLL table(nbxh char(16) not null,
xm varchar(8) not null,
zjhm varchar(20) null,
hzrq datetime not null)insert into @fzrll
select '37150010112','张三','37252619801226001','2002-10-25'
union all select '37150010112','李司','37152519780528152','2003-08-16'
union all select '37150010186','王珐','3715....','2002-05-16'
union all select '37150010186','肯发都','371523...','2001-11-28'declare @qydj table(nbxh char(16) not null)
insert into @qydj
select '37150010112'
union all select '37150010186'--查询测试
select a.*,b.* from @fzrll a inner join @qydj b on a.nbxh=b.nbxh
where a.hzrq=(select min(hzrq) from @fzrll where nbxh=a.nbxh)
------------ ------ ------------------- --------------------------- ------
37150010112 张三 37252619801226001 2002-10-25 00:00:00.000 37150010112
37150010186 肯发都 371523... 2001-11-28 00:00:00.000 37150010186 (所影响的行数为 2 行)