项目中遇到的情况,需要查询广告主所有的广告及登录的网站主是否申请的状态,广告与网站主的关系存在fh_ad_webmaster 表中。
- select a.id id,
- e.status status,
- a.name name,
- a.type type,
- a.picSize picSize,
- a.checkType checkType,
- a.startTime startTime,
- a.endTime endTime,
- c.AdMaster AdMaster,
- c.signPic signPic,
- c.webUrl webUrl,
- c.webName webName,
- d.id cid,
- d.catelogName catelogName,
- b.detailExplanation detailExplanation,
- b.squeezePercent squeezePercent,
- b.createTime createTime,
- b.type paytype,
- b.squeeze squeeze,
- c.companyName companyName,
- a.picPath picPath,
- a.linkText linkText
- from fh_ad_base a
- left join fh_ad_paynormal b on a.merchanter = b.adMasterId and a.getMoneyType=b.id
- left join fh_union_admaster c on c.AdMaster = a.merchanter
- left join fh_union_catalog d on c.webType = d.id
- left join fh_ad_webmaster e on e.acId = a.id and e.webMasterId =
- 'ff808081167b0fac01167b1d470e0004'
- where a.merchanter = 'ff808081167b0fac01167b1c794e0001' and
- a.status = 1
- order by id desc
- 或者
-
- select
- a.`name` adname,a.`type` adtype,a.`picSize` size,a.`putinType` puttype,
- a.`checkType` checktype,a.`startTime` starttime,a.`endTime` endtime,
- a.`picPath`,a.`linkText` linktext,
- b.`name` name ,b.`squeezeType` squeezeType,
- c.`AdMaster` admaster, c.`companyName` comanyname,c.`signPic` signpic,c.`webName` webname,
- c.`webUrl` weburl,
- d.`catelogName`,d.id cid,
- e.`status` entersataus
- from `fh_union_admaster` c ,`fh_union_catalog` d,`fh_ad_paynormal` b,
- `fh_ad_base` a left OUTER JOIN `fh_ad_webmaster` e on a.`id`=e.`acId` and e.`webMasterId`='ff808081167b0fac01167b1d470e0004'
- WHERE a.`getMoneyType`=b.`id` and c.`AdMaster`=a.`merchanter` and d.`id`=c.`webType`
- and a.`merchanter`='ff808081167b0fac01167b1c794e0001' and a.`status`=1