需求
cardealers 经销商表,
testdrive 试驾表,
查找试驾数超过100的经销商。显示账号信息和经销商名
思考SQL
以经销商表为主表,管理试驾表和会员表
以经销商表的id分组,分组之后可以查出每组经销商的试驾数。
然后having 选 试驾超过100的经销商
原生sql
SELECT
member.fullName,
cardealers.id,
cardealers.carShop,
count(*) AS countT
FROM
cardealers
LEFT JOIN testdrive ON testdrive.carDealersId = cardealers.id
LEFT JOIN member ON member.id = cardealers.memberId
WHERE
source = 'normalDrive' #试驾类型是普通试驾
AND approve = 'approved' #审核通过的试驾
AND reward = 0 #未发奖的
GROUP BY
cardealers.id
HAVING
countT > 100
ORDER BY
countT DESC
LIMIT 0,2
laravel 中SQL写法
laravel having中分页获取数据不能函数统计的别名,需要使用havingraw(函数)
orderby可以使用函数统计的别名
<?php
$cardealer = DB::table('cardealers')
->leftJoin('testdrive', 'testdrive.carDealersId', '=', 'cardealers.id')
->leftJoin('Member', 'cardealers.memberId', '=', 'Member.id')
->where('source','normalDrive')
->where('approve','approved');
if($model->reward==1){
$cardealer = $cardealer->where('reward', '>', 0);
}else{
$cardealer = $cardealer->where('reward', '=', 0);
}
$cardealer = $cardealer
->select('cardealers.*','Member.fullName', 'Member.username',DB::raw('count(cardealers.id) as countT'))
->groupBy('cardealers.id')
// ->having('countT','>',$model->rewardCount) //get()可以获取所有结果,但是分页获取报错,原因是laravel这里having不能使用别名,将别名改为ardealers.id是可以的。所以认为是别名的原因
// ->havingRaw('countT > 2') //分页报错:这里不能使用别名 countT
->havingRaw('count(cardealers.id) >= '.$model->rewardCount) //可以,havingraw(括号里只有一对单引号)
->OrderBy('countT','desc') //这里可以使用别名
// ->get();
->paginate(10);
return $cardealer;