select b.* from MainInfo as a inner join
otherInfo as b on a.contractno=b.contractno and a.regdate=b.regdate where a.cardid='1111'try
otherInfo as b on a.contractno=b.contractno and a.regdate=b.regdate where a.cardid='1111'try
这样查出来的是:
CardId Name ManageAddress
332623800411477 yhd0411 浙江
332623550129611 张三 浙江
332623800411477 yhd0411 上海
332623550129611 张三 浙江
332603670318156 李四 北京
我要查出来是
CardId Name ManageAddress
332623800411477 yhd0411 浙江
332623550129611 张三 浙江
332603670318156 李四 北京
maininfo where cardid=1111)
你沒有去試呀
select * from otherinfo a where regdate=(select max(regdate) from otherinfo where contractno=a.contractno and cardid=a.cardid) and contractno in(select contractno from
maininfo where cardid=1111)
otherInfo as b on a.contractno=b.contractno and a.regdate=b.regdate where a.cardid='1111' group by b.cardid
from otherInfo a
inner join MainInfo b
on a.contractno=b.contractno
and a.regdate=b.regdate
and b.cardid='1111'
and a.reddate in (select max(reddate) from otherInfo group by cardid,name,manageraddress)
select * from otherinfo a where regdate=(select max(regdate) from otherinfo where cardid=a.cardid) and contractno in(select contractno from
maininfo where cardid=1111)
CardId Name ManageAddress
332623800411477 yhd0411 浙江
332623550129611 张三 浙江
332623800411477 yhd0411 上海
332623550129611 张三 浙江
332603670318156 李四 北京
用group by 不行的啊,我的OtherInfo表其实有好多字段,这里只列出四个,不会让我全group by吧
不好意思,我刚从把你这条复制过来,改了一下改错了,你这个是行的不过,我mainInfo和otherInfo以三个字段关联的呢怎么办,还有一个字段是refundtimes
select * from otherinfo a where regdate=(select max(regdate) from otherinfo where cardid=a.cardid) and exists(select * from
maininfo where cardid=1111 and refundtimes=a.refundtimes and
contractno=a.contractno)就行了
maininfo where cardid=1111 and refundtimes=a.refundtimes and
contractno=a.contractno)
select a.cardid,a.name,a.manageraddress
from otherInfo a
inner join MainInfo b
on a.contractno=b.contractno
and a.regdate=b.regdate
and b.cardid='1111'
and a.reddate in (select max(reddate) from otherInfo group by cardid)