laravel 根据状态和不同的日期列获取用户计数

jc3wubiy 于 2022-11-26 发布在其他

关注(0)|答案(1)|浏览(120)

我必须根据状态和不同的日期列来计算用户的日期，下面是我的表。

topic_id| user_id | status |  created_at| updated_at | completed_at |
176     | 1       | 0      |2022-11-20  | NULL       | NULL         |
176     | 2       | 1      |2022-11-20  | 2022-11-21 | 2022-11-21   |
176     | 3       | 0      |2022-11-20  | NULL       | NULL         |
176     | 4       | 2      |2022-11-20  | 2022-11-21 | NULL         |
176     | 5       | 2      |2022-11-20  | 2022-11-21 | NULL         |
176     | 6       | 1      |2022-11-20  | 2022-11-22 | 2022-11-22   |

我希望输出

Date       | Total_Inactive | Total_Scheduled | Total_Active
2022-11-20 | 2              | 0               | 0
2022-11-21 | 0              | 2               | 1 
2022-11-22 | 0              | 0               | 1

total_Inactive计数基于status = 0和created_at列，total_Scheduled计数基于status = 2和updated_at列，total_Active计数基于status = 1和completed_at列
如何在单个查询中使用此功能？

laravel

来源：https://stackoverflow.com/questions/74573528/get-user-count-based-on-status-and-different-date-columns

1条答案

按热度按时间

htrmnn0y1#

您可以将CASE表达式与筛选聚合结合使用，以选择正确的日期：

select case status
          when 0 then created_at
          when 1 then completed_at
          when 2 then updated_at
       end as "date",
       count(*) filter (where status = 0) as total_inactive,
       count(*) filter (where status = 2) as total_scheduled,
       count(*) filter (where status = 1) as total_active
from the_table
group by "date"      
order by "date"

Online example

赞(0）回复(0）举报 2022-11-26

我来回答

laravel 根据状态和不同的日期列获取用户计数

1条答案

相关问题

热门标签

最新问答