按照最新的沟通记录时间排序

有2个表 一个用户表 一个沟通记录表
用户表有 id company jobid等字段
沟通表有addtime companyid 等字段 18000条
今天给个需求 要按照沟通记录排序
思路：先查沟通表最新的沟通记录 剔除不是最新的数据
SELECT companyid, MAX(addtime) maxtime FROM goutong GROUP BY companyid
查询结果7073条 查出来了
然后2表联查
select a.id,a.company,a.linkman,a.zhiwu,a.telphone,a.provinceid,c.maxtime from cominfo a left JOIN(SELECT companyid, MAX(addtime) maxtime FROM goutong GROUP BY companyid) c ON a.id=c.companyid where a.jobid=1373 order by c.maxtime ,a.id desc
一定要用left join
最终改完的sql

keyword=request("keyword")
provinceid=request("provinceid")
catid=request("catid")
yewu=request("yewu")
if keyword<>"" then condition=condition&" and (a.company like '%"&keyword&"%' or a.linkman like '%"&keyword&"%' or a.telphone like '%"&keyword&"%' or a.product like '%"&keyword&"%')"
if provinceid<>"" then condition=condition&" and a.provinceid="&provinceid&""
if catid<>"" then condition=condition&" and charindex('"&catid&"',catid)>0"
if yewu>0 then condition=condition&" and a.id=b.companyid"
if yewu=1 then
table=",lishi b"
ziduan=",b.endtime"
elseif yewu=2 then
table=",member b"
ziduan=",b.endtime"
elseif yewu=3 then
table=",wzad b"
ziduan=",b.endtime"
elseif yewu=4 then
table=",webinfo b"
ziduan=",b.endtime"
elseif yewu=5 then
table=",bzad b"
ziduan=",b.endtime"
elseif yewu=6 then
table=",magazine b"
ziduan=",b.endtime"
elseif yewu=7 then
table=",zzad b"
ziduan=",b.endtime"
end if

if keyword<>"" then pagecond=pagecond&"&keyword="&keyword&""
if provinceid<>"" then pagecond=pagecond&"&provinceid="&provinceid&""
if catid<>"" then pagecond=pagecond&"&catid="&catid&""

sql="select a.id,a.company,a.linkman,a.zhiwu,a.telphone,a.provinceid,a.addtime"&ziduan&" from cominfo a LEFT JOIN(SELECT companyid, MAX(addtime) maxtime FROM goutong GROUP BY companyid) c "&table&" on c.companyid=a.id where a.jobid="&session("a_job")&" and a.provinceid in ("&session("a_power")&") "&condition&" order by c.maxtime,a.id desc"

因为是asp写的。。。。so比较古老