这个问题很难解释,但我会尽力而为。我有两个桌子。这是特定时间之间的“计数列”查询

exchanges表结构:

  id   exchange       created_at            updated_at        deleted_at   start_time   close_time
 ---- ---------- --------------------- --------------------- ------------ ------------ ------------
   1   NSE        2018-04-18 06:22:11   2018-04-18 06:22:11   (NULL)       09:15:00     03:30:00
   2   BSE        2018-05-06 07:07:53   2018-05-06 07:07:53   (NULL)       00:00:00     00:00:00
   3   NYSE       2018-05-19 08:34:07   2018-05-19 08:34:07   (NULL)       00:00:00     00:00:00


finaltrades表结构:

 id   user_id   exchange_id   market_id   symbol_id      buy_datetime          sell_datetime      buy_rate   sell_rate   quantities
 ---- --------- ------------- ----------- ----------- --------------------- --------------------- ---------- ----------- ------------
   1         1             1           1          96   2018-05-25 18:13:26   0000-00-00 00:00:00       2205           0          100
   2         1             1           1          96   0000-00-00 00:00:00   2018-05-25 18:13:59          0        6680          100
   3         4             1           1          23   2018-05-25 18:16:27   0000-00-00 00:00:00          0           0           10
   4         1             1           1          96   2018-05-25 18:13:59   0000-00-00 00:00:00      50351           0           30
   5         1             1           1          15   0000-00-00 00:00:00   2018-05-25 18:34:46          0         100          150
   6         4             1           1         573   2018-05-26 09:29:17   2018-05-27 03:10:09         10          10           10
   7         1             1           1          15   2018-05-11 09:30:54   2018-05-25 18:34:56         40         100           40


buy_datetime表中有两列sell_datetimefinaltrades
buy_datetimesell_datetime,日期时间较短,我想将其与DB::raw('exchanges.start_time')进行比较。

public function firstHourTrades(){

        $user_id = Auth::user()->id;
        $data = DB::table('finaltrade')
            ->join('exchanges', 'finaltrade.exchange_id', '=', 'exchanges.id')
            ->select('finaltrade.*')
            ->where('finaltrade.user_id', $user_id)
            ->whereTime('finaltrade.LESS-DATE', '>=', DB::raw('exchanges.start_time'))
            ->whereTime('finaltrade.LESS-DATE', '<=', DB::raw("ADDTIME(exchanges.start_time, '01:00:00')"))
            ->count();

        return response()->json($data);
    }

最佳答案

我建议选择使用DB :: raw

$data = DB::table('finaltrade')
        ->join('exchanges', 'finaltrade.exchange_id', '=', 'exchanges.id')
        ->select(\DB::raw('finaltrade.*, IF(finaltrade.buy_datetime<finaltrade.sell_datetime, finaltrade.buy_datetime, finaltrade.sell_datetime) as less_date'))
        ->where('finaltrade.user_id', $user_id)
        ->whereTime('less_date', '>=', DB::raw('exchanges.start_time'))
        ->whereTime('less_date', '<=', DB::raw("ADDTIME(exchanges.start_time, '01:00:00')"))
        ->count();

10-06 05:16
查看更多