TP多条件sql查询，分组排序

 $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);