PHP Laravel查找2个查询构建器之间的差异

Question

我想找出我从查询构建器中获得的2个值之间的差异。获取的值必须在正确的校园和月份内。我想要找到上一个日期值，当前日期值和下一个日期值。之后，找到当前值与前一个值之间的差异(将其命名为usage)。问题是，当我访问查询构建器时，它对每个查询只显示1个值，而不是2个值。请帮帮我。

$metercurr = DB::table('smt_meter_reading')
                       ->join('smt_smart_meter', 'smt_meter_reading.smt_smart_meter_id', '=', 'smt_smart_meter.id')
                       ->select(DB::raw('SUM(actual_generation) as total1', 'smt_meter_reading.id', 'smt_smart_meter.serial_number'))
                       ->whereBetween('read_time',[$currDate, $nextDate])
                       ->where('smt_smart_meter.campus_id', $bill->campus_id)
                       ->groupBy('smt_meter_reading.smt_smart_meter_id')
                       ->get();
    
$meterprev = DB::table('smt_meter_reading')
                       ->join('smt_smart_meter', 'smt_meter_reading.smt_smart_meter_id', '=', 'smt_smart_meter.id')
                       ->select(DB::raw('SUM(actual_generation) as total2', 'smt_smart_meter.serial_number'))
                       ->whereBetween('read_time',[$prevDate, $currDate])
                       ->where('smt_smart_meter.campus_id', $bill->campus_id)
                       ->groupBy('smt_meter_reading.smt_smart_meter_id')
                       ->get();
    
$metername = DB::table('smt_meter_reading')
                       ->join('smt_smart_meter', 'smt_meter_reading.smt_smart_meter_id', '=', 'smt_smart_meter.id')
                       ->select('smt_smart_meter.serial_number')
                       ->where('smt_smart_meter.campus_id', $bill->campus_id)
                       ->get();
    
$previous = 0;
$current = 0;
$usage = 0;
$serialno;

foreach ($meterprev as $data) {
    $previous = $data->total2;
}

foreach ($metercurr as $data) { 
    $current = $data->total1;
 }

foreach ($metername as $data) {
    $serialno = $data->serial_number;
}
    
$usage = $current - $previous;

Answer 1

您需要在foreach语句中嵌套以下内容：

$usages = [];
foreach ($meterprev as $prevdata) {
    foreach ($metercurr as $currdata) {
        if ($prevdata->serial_number == $currdata->serial_number) {
            $usages = ($prevdata->total2 - $currdata->total1);
        }
    }
}

或者仅使用以下内容执行原始查询：

$sqlQuery = "

Select (second.total - first.total) as 'usage'
(
SELECT 
    'SUM(actual_generation) as total', 'smt_smart_meter.serial_number'
FROM 
    smt_meter_reading
JOIN 
    smt_smart_meter ON smt_smart_meter.id = smt_meter_reading.smt_smart_meter_id
WHERE 
    smt_smart_meter.campus_id = $campus_id AND
    read_time BETWEEN CAST($prevdate AS DATE) AND CAST($currdate AS DATE)
GROUP BY
    smt_meter_reading.smt_smart_meter_id
) as first,
(
    SELECT 
    'SUM(actual_generation) as total', 'smt_smart_meter.serial_number'
FROM 
    smt_meter_reading
JOIN 
    smt_smart_meter ON smt_smart_meter.id = smt_meter_reading.smt_smart_meter_id
WHERE 
    smt_smart_meter.campus_id = $campus_id AND
    read_time BETWEEN CAST($prevdate AS DATE) AND CAST($currdate AS DATE)
GROUP BY
    smt_meter_reading.smt_smart_meter_id
) as second
WHERE
    first.smt_smart_meter.serial_number = second.smt_smart_meter.serial_number
";

$result = DB::select(DB::raw($sqlQuery));

这应该会为您提供一个用法数组。这可能会更有效率或更有说服力，但希望这能为你指明正确的方向。