本文共 2083 字，大约阅读时间需要 6 分钟。
很多时候，数据库获取的信息并不是我们最终想要的，需要通过if进行处理。
where支持查询
having支持后查询（查询后的数据，再筛选）
代码如下：
if ($this->_post('dosearch','isset')) { // 搜索 if ($s_name = $this->_post('s_name','isset')) { $where['a.name'] = array('like','%'.$s_name.'%'); $this->assign('s_name',$s_name); } if ($s_category = $this->_post('s_category','isset')) { $where['a.category_id'] = $s_category; $this->assign('s_category',$s_category); } if ($s_status = $this->_post('s_status','isset')) { $having ='status ='.$s_status; // 只支持字符串 $this->assign('s_status',$s_status); } } if
// 获取商铺 $subQuery = M('User')->where(array('agent_id'=>$this->agent_id,'status'=>1)) ->field('id') ->select(false); $where['a.user_id'] = array('exp','in '.$subQuery); $where['a.status'] = 1; $list = M()->table('sh_store a') ->join('sh_goods b on a.id = b.store_id') ->join('sh_category c on a.category_id = c.id') ->join('sh_mall_shop d on a.id = d.store_id and d.mall_id = '.$this->mall['id']) ->where($where) ->group('a.id') ->having($having) ->field('a.id as store_id,a.user_id,a.name as store_name,c.name as category_name,a.logo,count(b.id) as goods_count,if(d.id > 0,"1","2") as status') ->select(); $this->assign('list',$list); sql原句如下：
SELECT a.id as store_id,a.user_id,a.name as store_name,c.name as category_name,a.logo,count(b.id) as goods_count,if(d.id > 0,"1","2") as status FROM sh_store a LEFT JOIN sh_goods b on a.id = b.store_id LEFT JOIN sh_category c on a.category_id = c.id LEFT JOIN sh_mall_shop d on a.id = d.store_id and d.mall_id = 9 WHERE ( a.category_id = '47' ) AND ( (a.user_id in ( SELECT `id` FROM `sh_user` WHERE ( `agent_id` = 13 ) AND ( `status` = 1 ) )) ) AND ( a.status = 1 ) GROUP BY a.id HAVING status =1
转载地址：http://wuikl.baihongyu.com/