获取每支战队前三名的平均成绩，原生SQL在这里，请问怎么用laravel的ORM模型复现呢

1. 运行环境

1). 当前使用的 Laravel 版本？

8.0

2). 当前使用的 php/php-fpm 版本？

PHP 版本：7.4

请教一下各位朋友，这句MySQL如何用ORM复现（主要是我对ORM的临时表使用掌握不熟练）：

SELECT
    dt_1.user_clan_id,
    dt_1.title,
    dt_1.clan_avatar,
    dt_1.is_captain,
    AVG(dt_1.speed_max) AS avg_speed_max,
    AVG(dt_1.runball_exponent) AS avg_runball_exponent,
    AVG(dt_1.exponent_molecular) AS avg_exponent_molecular,
    AVG(dt_1.marathon) AS avg_marathon,
    COUNT(
        IF (
            dt_1.user_clan_id = dt_1.user_clan_id,
            TRUE,
            NULL
        )
    ) AS count_t
FROM
    (
        SELECT
            user_clan_members.user_clan_id,
            user_clans.title,
            user_clans.clan_avatar,
            user_clan_members.is_captain,
            user_achievement.speed_max,
            user_achievement.runball_exponent,
            user_achievement.exponent_molecular,
            user_achievement.marathon
        FROM
            user_clans
        LEFT JOIN user_clan_members ON user_clans.id = user_clan_members.user_clan_id
        LEFT JOIN user_achievement ON user_clan_members.user_id = user_achievement.user_id
    ) AS dt_1
WHERE
    (
        SELECT
            COUNT(1)
        FROM
            (
                SELECT
                    user_clan_members.user_clan_id,
                    user_clans.title,
                    user_clans.clan_avatar,
                    user_clan_members.is_captain,
                    user_achievement.speed_max,
                    user_achievement.runball_exponent,
                    user_achievement.exponent_molecular,
                    user_achievement.marathon
                FROM
                    user_clans
                LEFT JOIN user_clan_members ON user_clans.id = user_clan_members.user_clan_id
                LEFT JOIN user_achievement ON user_clan_members.user_id = user_achievement.user_id
            ) AS dt_2
        WHERE
            dt_1.user_clan_id = dt_2.user_clan_id
        AND dt_1.speed_max < dt_2.speed_max
    ) < 3
GROUP BY
    dt_1.user_clan_id
ORDER BY
    dt_1.speed_max DESC

原生查询效果图：

这是我最后实现的答案（吐槽还不如我原生SQL），如下：

$param['limit'] = $param['limit'] ?? 15;
//建立临时表
$sql = DB::table('user_clans')
    ->leftJoin('user_clan_members','user_clans.id','=','user_clan_members.user_clan_id')
    ->selectRaw("
        user_clan_members.user_clan_id,
        user_clans.title,
        user_clans.address,
        user_clans.introduction,
        user_clans.telephone,
        user_clans.clan_avatar,
        user_clan_members.avg_speed_max,
        user_clan_members.avg_runball_exponent,
        user_clan_members.avg_exponent_molecular,
        user_clan_members.avg_marathon,
        1000/user_clan_members.avg_marathon AS avg_marathon_asc
    ")
    ->whereRaw("user_clans.status=1 AND user_clan_members.status=2");

//子查询统计各战队成员数量
$sql2 = DB::table('user_clans')
   ->leftJoin('user_clan_members','user_clans.id','=','user_clan_members.user_clan_id')
   ->selectRaw("COUNT(IF (user_clan_members.user_clan_id = user_clan_members.user_clan_id,TRUE,NULL))")
   ->whereRaw("user_clans. STATUS = 1 AND user_clan_members. STATUS = 2 AND dt_1.user_clan_id = user_clan_members.user_clan_id");

//需要展示的字段
$select = "dt_1.user_clan_id,dt_1.title,dt_1.address,dt_1.introduction,dt_1.telephone,
    CONCAT('".StaticDataController::$_server_url . "/',dt_1.clan_avatar) AS clan_avatar,
    ROUND(IF(COUNT(IF(dt_1.user_clan_id=dt_1.user_clan_id,TRUE,NULL))>=3,
    AVG(dt_1.avg_speed_max),0),0) AS avg_speed_max,'rpm' AS avg_speed_max_unit,
    ROUND(IF(COUNT(IF(dt_1.user_clan_id=dt_1.user_clan_id,TRUE,NULL))>=3,
    AVG(dt_1.avg_runball_exponent),0),2) AS avg_runball_exponent,
    ROUND(IF(COUNT(IF(dt_1.user_clan_id=dt_1.user_clan_id,TRUE,NULL))>=3,
    AVG(dt_1.avg_exponent_molecular),0)/1000,3) AS avg_exponent_molecular,
    'km' AS avg_exponent_molecular_unit,
    TRUNCATE(IF(COUNT(IF(dt_1.user_clan_id=dt_1.user_clan_id,TRUE,NULL))>=3,
    AVG(dt_1.avg_marathon),0),0) AS avg_marathon,
    IF(COUNT(IF(dt_1.user_clan_id=dt_1.user_clan_id,TRUE,NULL))>=3,
    1000/AVG(dt_1.avg_marathon),0) AS avg_marathon_asc";

//排序
$sql3 = DB::table(DB::raw("({$sql->toSql()}) as dt_2"))
     ->selectRaw("COUNT(1)")
     ->whereRaw("dt_1.user_clan_id = dt_2.user_clan_id AND dt_1.avg_speed_max < dt_2.avg_speed_max");

//最外层查询出结果
$userClanQuery = DB::table(DB::raw("({$sql->toSql()}) as dt_1"))
     ->selectRaw($select."({$sql2->toSql()}) AS count_t")
     ->whereRaw("({$sql3->toSql()})<3")
     ->groupByRaw("dt_1.user_clan_id")
     ->orderByRaw("avg_speed_max DESC");

 if (!empty($param['title'])){//加入战队名称搜索
      $userClanQuery = $userClanQuery->where('title','like','%'.$param['title'].'%');
 }

$userClan = $userClanQuery->paginate($param['limit']);