sql简写或优化

[ziyuanxian]

select 
(select path from chuangye_deepcate WHERE id=102)as path ,
(select catename from chuangye_deepcate WHERE id=102)as lanmutitle ,
(select lanmumuban from chuangye_deepcate WHERE id=102)as lanmumuban ,
(select keyword from chuangye_deepcate WHERE id=102)as keyword ,
(select zaiyao from chuangye_deepcate WHERE id=102)as zaiyao ,
(select jiaodiantu from chuangye_deepcate WHERE id=102)as jiaodiantu ,
(select iflink from chuangye_deepcate WHERE id=102)as iflink ,
(select linkurl from chuangye_deepcate WHERE id=102)as linkurl ,
(select ifdanye from chuangye_deepcate WHERE id=102)as ifdanye ,
(select content from chuangye_deepcate WHERE id=102)as content ,
(select pagesize from chuangye_deepcate WHERE id=102)as pagesize ,
n.id, n.title,n.smpic,n.addtime,n.zaiyao as conzaiyao from chuangye_news n where n.classid in(102,93,103,127,126) order by paixu desc,id desc

拼接多个(select xx from yy)as cc,这个可以一次多个吗，或者有什么更优秀的写法吗，感觉太长，感觉效率不是很高

[ACMAIN_CHM]

select m.* n.id, n.title,n.smpic,n.addtime,n.zaiyao as conzaiyao from chuangye_news n left join (select * from chuangye_deepcate WHERE id=102) m where n.classid in(102,93,103,127,126) order by paixu desc,id desc

[ziyuanxian]

引用 1 楼 ACMAIN_CHM 的回复:

select m.* n.id, n.title,n.smpic,n.addtime,n.zaiyao as conzaiyao from chuangye_news n left join (select * from chuangye_deepcate WHERE id=102) m where n.classid in(102,93,103,127,126) order by paixu desc,id desc

select m.*,
n.id, n.title,n.smpic,n.addtime,n.zaiyao as conzaiyao 
from chuangye_news n 
left join (select path,catename,lanmumuban,zaiyao,jiaodiantu,iflink,linkurl,ifdanye,content,pagesize from chuangye_deepcate WHERE id=102) m on 1=1
where n.classid in(102,93,103,127,126) 
order by paixu desc,id desc

发现要加一个on 1=1 才行，不知道这样安不安全呢

[ACMAIN_CHM]

select m.*, n.id, n.title,n.smpic,n.addtime,n.zaiyao as conzaiyao from chuangye_news n , (select path,catename,lanmumuban,zaiyao,jiaodiantu,iflink,linkurl,ifdanye,content,pagesize from chuangye_deepcate WHERE id=102) m where n.classid in(102,93,103,127,126) order by paixu desc,id desc