Laravel SQL一般错误：4015窗口函数仅在选择列表中允许和按子句订购

Question

我正在尝试在Laravel中获取下一行和上一行ID，但它不会让我这样做而不会得到此错误：

sqlstate [hy000]：常规错误：仅允许窗口功能4015 在选择列表和订单中，按子句（SQL：SELECT ORDERID，雇员， LEAD（OrderId，1）Over（OrderID）NextID，滞后（Orderid，1） （订购订单）从订单组中订购的先前 员工，隔壁，以前）

这是正在处理的代码

$order = DB::select(DB::raw(" 
    SELECT
        OrderID, 
        EmployeeID,
        LEAD(OrderID,1) OVER (
            ORDER BY OrderID
        ) nextID,
        lag(OrderID,1) OVER (
            ORDER BY OrderID
        ) previous
        
    FROM 
        orders

    group BY
        OrderID,
        EmployeeID,
        nextID,
        previous
    "))->orderby('OrderID', 'EmployeeID', 'nextID', 'previous')->get();

Answer 1

尝试

$order = DB::table('orders')
    ->select(
        'OrderID', 
        'EmployeeID',
        DB::raw('LEAD(OrderID,1) OVER (ORDER BY OrderID) as nextID'),
        DB::raw('LAG(OrderID,1) OVER (ORDER BY OrderID) as previous')
    )
    ->groupBy('OrderID', 'EmployeeID', 'nextID', 'previous')
    ->orderBy('OrderID', 'EmployeeID', 'nextID', 'previous')
    ->get();

Answer 2

希望这会帮助某人。

SQL查询

select *, lead(start_loc,1) over(PARTITION BY dri_id order by start_time asc) as next_start_ride from `drivers` group by `dri_id`

Laravel查询构建器

$data = DB::table('drivers')->select(
            '*', 
            DB::raw('lead(start_loc,1) over(PARTITION BY dri_id order by start_time asc) as next_start_ride')
        )->get();

echo "<pre>";
print_r($data);