如何使用原始查询laravel查找排名

deyfvvtc 于 2021-06-19 发布在 Mysql

关注(0)|答案(3)|浏览(616)

我想用laravel查询数据库中的排名，但不起作用
sql查询（它的作品）

SELECT
    'id',
    'name',
    'local_pic',
    'point',
    FIND_IN_SET( 'point', ( SELECT GROUP_CONCAT( 'point' ORDER BY 'point' DESC )
        FROM 'students' )) AS rank
FROM 'students'
ORDER BY rank

我试着用

$top_students = DB::table('students')
    ->select('id','name','local_pic','point',
    ->whereRaw("find_in_set('point',( SELECT GROUP_CONCAT( 'point' ORDER BY 'point' DESC ) FROM 'students' )) as rank"))
   ->orderBy('rank','DESC')
   ->get();

$top_students = DB::select("SELECT 'id','name','local_pic','point', FIND_IN_SET( 'point', ( SELECT GROUP_CONCAT( 'point' ORDER BY 'point' DESC ) FROM 'students' )) AS rank FROM 'students' ORDER BY rank");

这叫语法错误，怎么解决

mysql laravel rank syntax-error

来源：https://stackoverflow.com/questions/52888774/how-to-find-rank-using-raw-query-laravel

3条答案

按热度按时间

lstz6jyr1#

试试这个：

DB::select("
    SELECT
        'id',
        'name',
        'local_pic',
        'point',
        FIND_IN_SET( 'point', ( SELECT GROUP_CONCAT( 'point' ORDER BY 'point' DESC )
            FROM 'students' )) AS rank
    FROM 'students'
    ORDER BY rank
");

赞(0）回复(0）举报 2021-06-20

oewdyzsn2#

原始sql不包含where语句。另外，您正在使用 whereRaw() 添加原始选择查询，这是错误的。
尝试：

\DB::table('students')
    ->select(
        'id',
        'name',
        'local_pic',
        'point',
        \DB::raw("FIND_IN_SET( 'point', ( SELECT GROUP_CONCAT( 'point' ORDER BY 'point' DESC ) FROM 'students' )) AS rank")
    )
    ->orderBy('rank', 'DESC')
    ->get();

或

\DB::table('students')
    ->selectRaw("
        id,
        name,
        local_pic,
        point,
        FIND_IN_SET( 'point', ( SELECT GROUP_CONCAT( 'point' ORDER BY 'point' DESC ) FROM 'students' )) AS rank
    ")
    ->orderBy('rank', 'DESC')
    ->get();

赞(0）回复(0）举报 2021-06-20

gz5pxeao3#

用这个

$top_students = DB::table('students')
              ->select('id','name','local_pic','point')
              ->selectRaw("find_in_set('point',( SELECT GROUP_CONCAT( 'point' ORDER BY 'point' DESC ) FROM 'students' )) as rank")
              ->orderBy('rank','DESC')
              ->get();

您错过了一些结束括号，现在语法错误已修复，接下来您可以通过更改 ->get() 至 ->toSql()

赞(0）回复(0）举报 2021-06-20

我来回答

如何使用原始查询laravel查找排名

3条答案

相关问题

热门标签

最新问答