获取按日分组的行的最小数和最大行数

Question

我想返回每一张唱片的Min和Max，每一张唱片都是以Laravel雄辩的口才按日分组的，下面是：

表结构：Rate

id     buy_rate     sell_rate     market_currency_id     created_at
==     ========     =========     ==================     ==========
1      10           7             44                     2020-10-10
2      10           9             44                     2020-10-10
3      10           8             44                     2020-10-10
4      10           12            44                     2020-10-11
5      10           14            44                     2020-10-11
6      10           13            44                     2020-10-11

现在我得到了这样的数据：

$rates = Rate::
select('sell_rate', \DB::raw("DATE_FORMAT(created_at, %Y-%m-%d) day))
->where('market_currency_id', '=', 44)
->whereBetween('created_at', [2020-10-10, 2020-10-11])
->orderBy('created_at', 'ASC')
->get()
->toArray();

以上代码返回：

0 => array:2 [▼
    "sell_rate" => "8"
    "day" => "2010-10-10"
]
1 => array:2 [▼
    "sell_rate" => "12"
    "day" => "2010-10-11"
]

现在，我想要的是这样的东西：

0 => array:2 [▼
    "sell_rate" => "8"
    "day" => "2010-10-10"
    "min_rate" => "7"
    "max_rate" => "9"

]
1 => array:2 [▼
    "sell_rate" => "13"
    "day" => "2010-10-11"
    "min_rate" => "12"
    "max_rate" => "14"
]

我尝试了很多方法，但我无法得到我想要的，我尝试了这样一个原始的选择：

$rates = Rate::
select('sell_rate', \DB::raw("DATE_FORMAT(created_at, %Y-%m-%d) day))
->selectRaw("MIN(sell_rate) AS min_rate, MAX(sell_rate) AS max_rate")
->where('market_currency_id', '=', 44)
->whereBetween('created_at', [2020-10-10, 2020-10-11])
->orderBy('created_at', 'ASC')
->get()
->toArray();

但没有得到我想要的，我也不能单独做，因为有数千行，谢谢提前！

Answer 1

你还需要按日分组：

$rates = Rate::select(DB::raw("DATE_FORMAT(created_at, %Y-%m-%d) day"))
    ->selectRaw("MIN(sell_rate) AS min_rate, MAX(sell_rate) AS max_rate")
    ->where('market_currency_id', '=', 44)
    ->whereBetween('created_at', ['2020-10-10', '2020-10-11'])
    ->groupBy(DB::raw('day'))
    ->orderBy(DB::raw('day'))
    ->get()
    ->toArray();

Answer 2

你可以试试这个：

$rates = Rate::
select('sell_rate', \DB::raw("DATE_FORMAT(created_at, %Y-%m-%d) day"), \DB::raw("MIN(sell_rate) as min_rate"), \DB::raw("MAX(sell_rate) as max_rate"))
->where('market_currency_id', '=', 44)
->whereBetween('created_at', [2020-10-10, 2020-10-11])
->orderBy('created_at', 'ASC')
->groupBy(DB::raw('day'))
->get()
->toArray();