java sql多表查询语句_SQL语句中的多表查询记录

//子查询

select * from site_product

where productid > '286585'

and state = '1'

and siteid <> '750473'

and image <> ''

and siteid in (SELECT siteid from site_company WHERE state=1)

order by  productid desc

LIMIT 1 offset 0;

//左联查询

select * from (select companyid as tt from base_companymember order by random() Limit "+num+") as a LEFT JOIN base_company ON tt=companyid

//上一个， 下一个

select mediaid,medianame,filepath

FROM (SELECT row_number() OVER (ORDER BY displayorder DESC,mediaid desc)as rowno,mediaid,medianame,displayorder,filepath FROM site_media WHERE state>=0 and siteid=3854719) as aaa

WHERE

aaa.rowno>(SELECT rowno FROM (SELECT row_number() OVER (ORDER BY displayorder DESC,mediaid desc)as rowno,mediaid,medianame,displayorder,filepath FROM site_media WHERE state>=0 and siteid=3854719) as bbb WHERE bbb.mediaid=899)

ORDER BY aaa.rowno

LIMIT 1;

posted on 2012-11-02 15:28 老天 阅读(83) 评论(0)  编辑  收藏