ThinkPhp实现复杂sql

以下为平时工作中使用thinkphp实现的一些较复杂的sql查询，在此记录，希望可以帮助到其他使用thinkphp框架的小伙伴

---

一 根据if条件去sum

Mysql中count()函数的一般用法是统计字段非空的记录数，所以可以利用这个特点来进行条件统计，注意这里如果字段是NULL就不会统计，但是false是会被统计到的，记住这一点!!!

	Db::name('server_bill')
    ->where(['user_id'=>$userId,'status'=>1])
    ->field(["sum(if(type in (1,2),money,NULL)) as income",
    "sum(if(type=3,money,NULL)) as expend"])
    ->find();

二 left联表，求sum

	SalaryUser::alias('s')
    ->join('perf_tdx_week_award p', 'p.uid=s.id', 'left')
    ->field('SUM(p.aa_service_fee) AS aa_service_fee, 
      SUM(p.new_mc) AS new_mc, s.group, s.name, s.job,
      IFNULL(aa_service_fee, 0), IFNULL(new_mc, 0)')
    ->group('s.id')
    ->select();

三 实现mysql的case和then

SELECT
	account,
	mobile,
	( CASE verified_status WHEN 1 THEN '待审核' WHEN 2 THEN '待审核' ELSE '已通过' END ) 审核状态 
FROM
	y_user_base 
	LIMIT 20;

查询结果如下

tp写法

M('Member')
 ->alias('a')
 ->field(array('a.id','a.name','d.url','CASE
    WHEN b.enterprise_id = '.$member_id.' THEN 1
    WHEN b.enterprise_id1 = '.$member_id.' THEN 2
    WHEN b.enterprise_id2 = '.$member_id.' THEN 3
    ELSE 5 END AS flag'))
 ->join('left join __PERSON_INFO__ b on a.id=b.member_id')
 ->join('left join __WUSER__ c on c.id = a.wxuser_id')
 ->join('left join __IMG__ d on d.id = c.headimgurl')
 ->where($data)
 ->order('flag,a.id DESC')
 ->limit($firstRow,$count)
 ->select();

四 根据字段指定值进行排序(orderRaw)

$list = McRechargeLog::alias('mrl')
           ->join('mc_base mb', 'mrl.mid=mb.id', 'LEFT')
           ->join('admin a', 'mb.sell_id=a.id', 'LEFT')
           ->where($where)
           ->field('mrl.*, mb.account, mb.sell_id,mb.mc_type, a.realname')
           ->orderRaw("field(mrl.status,1,2,-1)") //按照1 2 -1来排序
           ->order('mrl.create_time asc')
           ->limit($limit_start, $limit_length)
           ->select();

五 根据某个字段不同的值进行不同的排序方式(orderRaw、case)

mysql写法

SELECT
	* 
FROM
	`y_task_logic` `tl` 
WHERE
	`tl`.`status` = 3 
	AND `tl`.`module_type` = 'free' 
	AND `tl`.`cut_off` = 0 
ORDER BY
	tl.remain_count DESC,
CASE
	WHEN tl.remain_count = 0 THEN
	tl.task_start 
	END ASC,
CASE
		WHEN tl.remain_count > 0 THEN
		tl.task_start 
END ASC 
	LIMIT 0,
10

tp写法

$list = TaskLogic::alias('tl')
    ->where($where)
    ->orderRaw('tl.remain_count desc, 
    case when tl.remain_count = 0 then tl.task_start end asc, 
    case when tl.remain_count > 0 then tl.task_start end asc')
    ->limit($limit_start, $limit_length)
    ->select();

六 子查询

mysql写法

SELECT
    ub.account,
    ub.mobile,
    ui.real_name,
    ui.user_ww,
    ( SELECT count( * ) FROM y_user_base ub2 WHERE ub2.invited_uid = ub.id ) num
FROM
    y_user_base ub
    LEFT JOIN y_user_info ui ON ub.id = ui.uid
ORDER BY
    num DESC;

tp写法

UserBase::alias('ub')
     ->join('user_info ui', 'ub.id=ui.uid', 'LEFT')
     ->field("ub.id, ub.account, ub.mobile, ui.user_ww, ui.real_name, 
     (select count(*) from y_user_base ub2 where ub2.invited_uid=ub.id) as num")
     ->where($where)
     ->limit($limit_start, $limit_length)
     ->order('ub.create_time desc')
     ->select();