$k=M('order a');
$bj=$k->join("left join __CHANGE__ b on b.tb_name='order'and a.order_id=b.tb_id ")
->join("left join __USERS__ c on c.user_id=a.user_id")
->join("left join __NATION__ e on e.code=a.city")
->join("left join __CAR__ f on f.car_id=a.car_category")
->join("left join __OFFER__ d on d.order_id=a.order_id")
->where('a.is_active=1 and a.car_category in ('.$car_category_string.") and a.order_status=0 and a.cut_off_time>".time())
->field('a.order_id,a.car_category,c.nickname,b.change_time as time,c.city as city,a.order_sn,d.offer_sn,e.city,f.scs')
->order("b.change_time")
->select();
发现分组有点乱,添加了 case 分组
$k=M('order a'); $bj=$k->join("left join __CHANGE__ b on b.tb_name='order'and a.order_id=b.tb_id and b.user_id=$this->user_id") ->join("join __USERS__ c on c.user_id=a.user_id") ->join("join __NATION__ e on e.code=a.city") ->join("join __CAR__ f on f.car_id=a.car_category") ->join("left join __OFFER__ d on d.order_id=a.order_id and d.user_id=$this->user_id") ->where('a.is_active=1 and a.car_category in ('.$car_category_string.") and a.order_status=0 and a.cut_off_time>".time()) ->field('a.order_id,a.add_time,a.car_category,c.nickname,b.change_time as time,c.city as city,a.order_sn,d.offer_sn,e.city,f.scs,CASE WHEN b.change_time is null && d.offer_sn IS NULL THEN 0 when b.change_time is NOT null && d.offer_sn IS NULL THEN 1 ELSE 2 END AS flag') // ->group('d.offer_sn is null') ->order("flag,a.add_time desc") ->select();
mysql 语句
SELECT a.order_id, a.add_time, a.car_category, c.nickname, b.change_time AS time, c.city AS city, a.order_sn, d.offer_sn, e.city, CASE WHEN b.change_time is null && d.offer_sn IS NULL THEN 0 when b.change_time is NOT null && d.offer_sn IS NULL THEN 1 ELSE 2 END AS flag, f.scs FROM beir_order a LEFT JOIN beir_change b ON b.tb_name = 'order' AND a.order_id = b.tb_id AND b.user_id = 2601 JOIN beir_users c ON c.user_id = a.user_id JOIN beir_nation e ON e. CODE = a.city JOIN beir_car f ON f.car_id = a.car_category LEFT JOIN beir_offer d ON d.order_id = a.order_id AND d.user_id = 2601 WHERE ( a.is_active = 1 AND a.car_category IN (4, 5, 19, 23, 24) AND a.order_status = 0 AND a.cut_off_time > 1517447807 ) ORDER BY flag, a.add_time DESC
另外再贴一个恰维后台某个调动下载页面的代码
$model=M('category');
$cat1=$model->where("pid=52")->order("id desc")->select();
foreach ($cat1 as $k=>$value){
$arr[]=$value['id'];
}
$arr=implode(",",$arr);
$condition['sid'] = array('in', $arr);
$cat2=M('article')->Distinct(true)->where($condition)->field('keywords')->select();
//分页开始
$count=M('article')->where($condition)->count();
$p = intval($p) > 0 ? $p : 1;
$pagesize = 10;#每页数量
$offset = $pagesize * ($p - 1);//计算记录偏移量
$page = new \Think\Page($count, $pagesize);
$page = $page->show();
$this->assign('page', $page);
$data=M('article a')->join("left join __CATEGORY__ b on a.sid=b.id")->field("a.*,b.name")->where($condition)->order("aid desc")->limit($offset . ',' . $pagesize)->select();
foreach ($data as $key=>$value){
$arr=$value['content'];
$arr=explode(" ",$arr);
$arr=explode("=",$arr[2]);
$arr=str_replace('"', '', $arr[1]);
$data[$key]['content']=$arr;
}
$this->assign("data",$data);
$this->assign("cat1",$cat1);
$this->assign("cat2",$cat2);