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