排序顺序参数

nzkunb0c  于 2021-06-15  发布在  Mysql
关注(0)|答案(1)|浏览(326)

我试图将变量传递给 order by 一段 DB::select(DB::raw('')) 通过这样做:

....order by :sort :order;'), array('sort' => $sort, 'order' => $order));

有没有什么原因让我的电脑出现语法错误 :order ?
如果我替换 :sort 带say的列 qtSTDDEV ,出现错误 :order 仍然坚持
代码

$sort  = 'qtSTDDEV';
$order = 'order';

DB::select(DB::raw('SELECT mid         as mID,
   round((x.qty_sum / x.qty_count), 5) as qtAVG,
   round(x.qty_stddev, 5)              as qtSTDDEV,
   x.qty_count                         as qtCOUNT,
   round((x.rel_sum / x.rel_count), 5) as relAVG,
   round(x.rel_stddev, 5)              as relSTDDEV,
   x.rel_count                         as relCOUNT,
FROM (SELECT mid,
         SUM( mt = 'qt' )   as qty_count,
         SUM(CASE WHEN mt = 'qt' THEN rt END)  as qty_sum,
         STD(CASE WHEN mt = 'qt' THEN rt END)  as qty_stddev,
         SUM( mt = 'rel' ) as rel_count,
         SUM(CASE WHEN mt = 'rel' THEN rel END) as rel_sum,
         STD(CASE WHEN mt = 'rel' THEN rel END) as rel_stddev
  FROM t_r r
right join t_m t on t.mid = r.mid
  GROUP BY mid
 ) x
right join m_k m on m.mid = x.mid
right join k_d k on k.kid = m.kid
order by :sort :order;'), array('sort' => $sort, 'order' => $order));
x6h2sr28

x6h2sr281#

您发布的代码不是有效的php,因此很难提供100%确定的答案。
但是,可以肯定的是,列名不能传递绑定参数。您需要从php代码生成sql查询的这一部分。
另外,在 order by 子句需要用逗号分隔。
我猜你想替换这个:

order by :sort :order;'), array('sort' => $sort, 'order' => $order));

使用:

order by ' . $sort . ', ' . $order . ';'));

相关问题