TP操作

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

1 根据条件，if求sum

$result = 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();

　　

 2 left联表，求sum

$data = 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();

3 使用mysql的case和then

select account,mobile,(case verified_status when 1 then '待审核' when 2 then '待审核' else '已通过' end) 审核状态 from y_user_base limit 20;

　　

 mysql写法：

select ui.user_id,ui.user_name,
case ui.user_type
    when 'company_legal_person' then '企业法人'
    when 'gov_leader' then '政府领导'
    when 'gov_grid_worker' then '网格员'
    when 'gov' then '政府用户'
    when 'company' then '企业用户'
    when 'company_safer' then '企业安全员'
    when 'company_safe_manager' then '企业安全管理员'
    when 'company_charger' then '企业安全负责人'
    when 'gov_fulltime_worker' then '专职人员'
    else '其他' end as user_type,
ui.mobile from user_info ui
    left join login_user_relation lur on lur.user_id = ui.user_id 
    left join login_info li on li.login_id=lur.login_id
    where lur.status = '1' and ui.status = '1' and li.status='1'
    and ui.company_id = 'iksbe634ajogetpn'

tp写法：

$member = 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();

4 根据字段指定值进行排序(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)")
            ->order('mrl.create_time asc')
            ->limit($limit_start, $limit_length)
            ->select();

5 根据某个字段不同的值进行不同的排序方式

原生sql:

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

6 子查询

原生sql:

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

7 子查询+having

原生sql：

SELECT
	ub.account,
	ub.mobile,
	ui.real_name,
	ub.prove_group,
	ub.invited_uid,
	g.NAME,
	( SELECT min( t2.create_time ) FROM y_task_order t2 WHERE t2.uid = ub.id ) AS f_time,
	count( t.id ) AS total,
	count( IF ( t.STATUS = 4, t.id, NULL ) ) AS finish,
	sum( t.reality_price ) AS t_sum 
FROM
	`y_user_base` `ub`
	LEFT JOIN `y_group` `g` ON `ub`.`group_id` = `g`.`id`
	LEFT JOIN `y_user_info` `ui` ON `ub`.`id` = `ui`.`uid`
	LEFT JOIN `y_task_order` `t` ON `ub`.`id` = `t`.`uid` 
	AND t.STATUS > 0 
	AND t.create_time BETWEEN 1594137600 
	AND 1596816000 
GROUP BY
	`ub`.`id` 
HAVING
	total = 1 
	AND f_time BETWEEN 1594137600 
	AND 1596816000 
ORDER BY
	`ub`.`id` DESC 
	LIMIT 0,
	10

　　tp写法：

$where_t = ' t.create_time between ' .  时间戳 . ' and ' . 时间戳;
$list = UserBase::alias('ub')
                ->join('group g', 'ub.group_id=g.id', 'LEFT')
                ->join('user_info ui', 'ub.id=ui.uid', 'LEFT')
                ->join('task_order t', ['ub.id=t.uid', 't.status > 0', $where_t . ' '], 'LEFT')
                ->field("ub.account, ub.mobile, ui.real_name, ub.prove_group,ub.invited_uid,g.name,
            (select min(t2.create_time) from y_task_order t2 where t2.uid=ub.id) as f_time,
            count(t.id) as total, count(IF(t.status=4,t.id,NULL )) as finish, sum(t.reality_price) as t_sum")
                ->where($where)
                ->group('ub.id')
                ->having($having)
                ->order('ub.id desc')
                ->limit($limit_start, $limit_length)
                ->select();

7 where和whereOr同时使用

我想实现的sql：

UPDATE `y_todo` 
SET `todo_status` = - 1 
WHERE
	( `todo_status` = 1 AND `is_redo` = - 1 AND `end_time` BETWEEN 1 AND 1637306996224 ) 
	OR ( `todo_status` = 1 AND `is_redo` = - 1 AND `end_time` = 0 AND `start_time` < 1637306996224 )

于是我用Tp6写了如下代码：

Todo::where([['todo_status', '=', 1], ['is_redo', '=', -1]])
            ->where([['end_time', 'BETWEEN', [1, getMillisecond()]]])
            ->whereOr([['end_time', '=', 0 ], ['start_time', '<', getMillisecond()]])
            ->update(['todo_status' => -1]);

结果生成了如下sql语句，啥玩意！！！这显然不是我想要的：

UPDATE `y_todo` 
    SET `todo_status` = - 1 
WHERE
`todo_status` = 1 
    AND `is_redo` = - 1 
    AND `end_time` BETWEEN 1 AND 1637289945439 
    OR `end_time` = 0 
    OR `start_time` < 1637289945439

正确写法如下：

        Todo::where(function ($query){
            $query->where([['todo_status', '=', 1], ['is_redo', '=', -1], ['end_time', 'BETWEEN', [1, getMillisecond()]]]);
        })->whereOr(function ($query){
            $query->where([['todo_status', '=', 1], ['is_redo', '=', -1], ['end_time', '=', 0 ], ['start_time', '<', getMillisecond()]]);
        })->update(['todo_status' => -1]);