将两个表与所有记录联接 [英] Join two tables with all records

问题描述

我正在尝试在Laravel控制器代码中联接两个表，并在一个 Datatable 中查看它们.

I'm tryng to join two tables in my Laravel controller code, and view them in one Datatable.

表1

+--------------------+---------+
|     recordtime     | tempout |
+--------------------+---------+
| 4.12.2020 10:00:00 |     1.1 |
| 4.12.2020 10:30:00 |     1.2 |
| 4.12.2020 11:00:00 |     1.3 |
| 4.12.2020 11:30:00 |     1.4 |
| 4.12.2020 12:00:00 |     1.5 |
+--------------------+---------+

表2

+--------------------+---------+
|     recordtime     | tempout |
+--------------------+---------+
| 4.12.2020 10:00:00 |     2.1 |
| 4.12.2020 11:00:00 |     2.3 |
| 4.12.2020 12:00:00 |     2.5 |
| 4.12.2020 13:00:00 |     2.6 |
| 4.12.2020 14:00:00 |     2.7 |
+--------------------+---------+

当我使用此代码时:

$results  = Tablemodel1::whereBetween('table1.recordtime', $dateScope)
    ->selectRaw('table1.tempout,table2.tempout as tempoutstamb,table2.recordtime')
    ->leftJoin('table2', function($join){
        $join->on('table1.recordtime', '=', 'table2.recordtime');
    })
    ->orderBy('table1.recordtime', 'ASC')
    ->get();

return Datatables::of($results)
    ->make(true);

它为我提供了所有等于核心响应记录时间的记录，并且每半小时就有一个附加记录，但具有 table1 中的空(无效日期)值.如何显示其日期而不是null(无效日期)?

It's giving me all records that equals to the coresponding recordtime and with aditional records that are on every half hour but with null(invalid date) values from table1. How to display their date instead of null(invalid date)?

+--------------------+---------+--------------+
|     recordtime     | tempout | tempoutstamb |
+--------------------+---------+--------------+
| invalid date       |     1.2 |            - |
| invalid date       |     1.4 |            - |
| 4.12.2020 10:00:00 |     2.1 |          1.1 |
| 4.12.2020 11:00:00 |     2.3 |          1.3 |
| 4.12.2020 12:00:00 |     2.5 |          1.5 |
+--------------------+---------+--------------+

添加了基于@ miken32答案的有效Laravel查询:

added working Laravel query based on @miken32 answer:

  $results2  = Tablemodel1::whereBetween('table1.recordtime', $dateScope)
    ->selectRaw('table1.recordtime')
    ->selectRaw('max(table1.tempout) as tempout')
    ->selectRaw('max(table2.tempout) as tempoutstamb')        
    ->leftJoin('table2', function($join){
        $join->on('table1.recordtime', '=', 'table2.recordtime');
    })
    ->groupBy('table1.recordtime');
  $results = Tablemodel2::whereBetween('table2.recordtime', $dateScope)
    ->selectRaw('table2.recordtime')
    ->selectRaw('max(table1.tempout) as tempout')
    ->selectRaw('max(table2.tempout) as tempoutstamb')        
    ->leftJoin('table1', function($join){
        $join->on('table1.recordtime', '=', 'table2.recordtime');
    })
    ->groupBy('table2.recordtime')       
    ->orderBy('recordtime', 'ASC')
    ->union($students2)
    ->get();

推荐答案

以下是一些SQL，做到了:

SELECT table1.recordtime, table1.tempout, table2.tempout AS tempoutstamb
FROM table1
LEFT JOIN table2 ON (table1.recordtime = table2.recordtime)

UNION

SELECT table2.recordtime, table1.tempout, table2.tempout AS tempoutstamb
FROM table2
LEFT JOIN table1 ON (table1.recordtime = table2.recordtime)

ORDER BY recordtime

您正在寻找完全连接，但是MySQL 不这样做.因此，我们使用 UNION 查询对其进行了伪造.

You're looking for a full join, but MySQL doesn't do those. So we fake it with a UNION query.

要在Laravel中使用，可能最简单的方法是将整个内容包装成一个原始语句.

For use in Laravel, probably easiest to just wrap the whole thing in a raw statement.

这篇关于将两个表与所有记录联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！