這更像是一個 SQL 問題，而不是 Laravel 問題。
我想根據我在該 領域的User模型完成以下類似的事情。created_at（資料庫中的表用戶）
| created_at | 用戶身份 |
|---|---|
| 2022-04-30 | 1 |
| 2022-05-02 | 2 |
| 2022-05-03 | 4 |
| 日期 | created_users_to_this_date | total_users_created_to_date |
|---|---|---|
| 2022-04 | 1 | 1 |
| 2022-05 | 2 | 3 |
關于如何做到這一點的任何想法？
到目前為止我所做的（使用 Eloquent ORM）：
User::query()
->selectRaw("COUNT(*) created_users_to_this_date, DATE_FORMAT(created_at, '%Y-%m') date")
->orderBy('date')
->groupBy('date')
->get();
等效的 SQL 請求
select COUNT(*) created_users_to_this_date, DATE_FORMAT(created_at, '%Y-%m') date from `users` where `users`.`deleted_at` is null group by `date` order by `date` asc
從而回傳
| 日期 | created_users_to_this_date |
|---|---|
| 2022-04 | 1 |
| 2022-05 | 2 |
我感謝你的幫助
uj5u.com熱心網友回復：
如果你的mysql版本支持window function，可以嘗試使用SUMwindow function做累計計數
DB::table(DB::raw('(select COUNT(*) created_users_to_this_date, DATE_FORMAT(created_at, \'%Y-%m\') date
from `users`
where `users`.`deleted_at` is null
group by `date`) t1'))
->select('created_users_to_this_date','date',DB::raw('SUM(created_users_to_this_date) OVER(ORDER BY date) total_users_created_to_date'))
->get();
uj5u.com熱心網友回復：
你的等效 sql 將是
SELECT DATE ,
@running_number:=@running_number created_users_to_this_date AS created_users_to_this_date
FROM (SELECT
COUNT(*) AS created_users_to_this_date,
DATE_FORMAT(created_at, '%Y-%m') DATE
FROM
users
where users.deleted_at is null
GROUP BY `date`
ORDER BY `date` ASC ) final
JOIN (SELECT @running_number:=0) rn
uj5u.com熱心網友回復：
User::query()
->select('
DB::raw('COUNT(DATE_FORMAT(created_at, \'%Y-%m\') = DATE_FORMAT(now(), \'%Y-%m\')) as created_users_to_this_date'),
DB::raw('COUNT(DATE_FORMAT(created_at, \'%Y-%m\') <= DATE_FORMAT(now(), \'%Y-%m\')) as total_users_created_to_date'),
DB::raw('DATE_FORMAT(created_at, '%Y-%m') as date')
')->orderBy('date')->groupBy('date')->get();
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/471386.html
上一篇：如何在MySQL中連接字串的值