怎么样改，才能提高sql效率

[shuizhilang]

SELECT *,
(select top 1 addtime from zu_job_money_detail where zu_job_money_detail.comid=zu_job_company.id and flag=1 order by intid desc)as md_zjjftime,
(select top 1 addtime from zu_job_money_detail where zu_job_money_detail.comid=zu_job_company.id and flag=1 order by intid asc)as md_dyckaitong,
(select top 1 validity from zu_job_money_detail where zu_job_money_detail.comid=zu_job_company.id and flag=1 order by intid desc)as md_vipendtime,
(select top 1 paytype from zu_job_money_detail where zu_job_money_detail.comid=zu_job_company.id and flag=1 order by intid desc) as md_paytype
FROM zu_job_company order by id desc

[DBA_磊仔]

select * from zu_job_company a 
cross apply 
(select top(1)addtime md_zjjftime,
		validity md_vipendtime,paytype md_paytype
	from zu_job_money_detail b 
	where b.comid=a.id and b.flag=1 
	order by b.intid desc)b
cross apply 
(select top(1)addtime md_dyckaitong
	from zu_job_money_detail b 
	where b.comid=a.id and b.flag=1 
	order by b.intid asc)c

[shuizhilang]

今天没人吗？

[dodolzc10]

[Quote=引用 1 楼 的回复:]
今天没人吗？
[/Quote]
周六,大牛都放假去了

[sql2015]

select a.*,
		c.addtime as md_zjjftime,
		d.addtime as md_dyckaitong,
		c.validity as md_vipendtime,
		c.paytype as md_paytype
from zu_job_company a
left join 
(
	select comid,MAX(intid) as maxid,MIN(intid) as minid from zu_job_money_detail
	where flag=1
	group by comid
) b  on a.id=b.comid
left join zu_job_money_detail c on b.maxid = c.id 
left join zu_job_money_detail d on b.minid = d.id

[叶子]

[Quote=引用 3 楼 的回复:]

SQL code

select a.*,
c.addtime as md_zjjftime,
d.addtime as md_dyckaitong,
c.validity as md_vipendtime,
c.paytype as md_paytype
from zu_job_company a
left join
(
……
[/Quote]

#3的思路是可以的，貌似后面的条件有点小问题。

select a.*,
        c.addtime as md_zjjftime,
        d.addtime as md_dyckaitong,
        c.validity as md_vipendtime,
        c.paytype as md_paytype
from zu_job_company a
left join 
(
    select comid,MAX(intid) as maxid,MIN(intid) as minid from zu_job_money_detail
    where flag=1
    group by comid
) b  on a.id=b.comid
left join zu_job_money_detail c on b.maxid = c.intid  --字段应该是intid
left join zu_job_money_detail d on b.minid = d.intid  --字段应该是intid
order by a.id desc --加上order 就你的结果一样了。

[shuizhilang]

3楼的写法确实很好，4楼补充的"c.intid --字段应该是intid”也对