laravel：从mysql获取通过和失败结果的总数

zqdjd7g9 于 2021-06-19 发布在 Mysql

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

我陷入了这个问题。我在数据库中有一个表，其中os\ U flash\ U结果可以是通过或失败。我必须以带有日期的图形形式显示失败和通过结果的数量。
我的表格结构和数据如下：

mysql> select * from mobile_prod_pograms;
+----+-----------+-----------------+------------------------+---------+----------+---------------------+---------------------+
| id | device_id | os_flash_result | action                 | station | build_id | created_at          | updated_at          |
+----+-----------+-----------------+------------------------+---------+----------+---------------------+---------------------+
|  2 | 1         | FAIL            | Mobile Production Prog |       1 | mp1      | 2018-09-27 00:00:00 | 2018-09-27 00:00:00 |
| 19 | 1         | PASS            | Mobile Production Prog |       1 | mp1      | 2018-09-27 00:00:00 | 2018-09-27 00:00:00 |
| 20 | 1         | PASS            | Mobile Production Prog |       1 | mp1      | 2018-09-27 00:00:00 | 2018-09-27 00:00:00 |
+----+-----------+-----------------+------------------------+---------+----------+---------------------+---------------------+
3 rows in set (0.00 sec)

我使用的是laravel，所以我有wirtten下面的查询来获得pass结果的计数。

$mobile_chart_query =  DB::table('mobile_prod_pograms')
            ->select(DB::raw('DATE(created_at) as date'), DB::raw('count(id) as total'))
            ->where('os_flash_result','=','PASS')
            ->where('created_at','>=','2018-09-17')
            ->groupBy('date')
            ->get();

上面的查询工作正常。但我想在一个带有日期的查询中获得失败和通过结果计数。我如何实现这一点？任何帮助都将不胜感激。。。

mysql laravel-5.6 Graph

来源：https://stackoverflow.com/questions/52547493/laravel-get-count-of-total-number-of-pass-and-fail-results-from-mysql

1条答案

按热度按时间

cx6n0qe31#

您可以使用条件聚合：

$mobile_chart_query =  DB::table("mobile_prod_pograms")
    ->select(DB::raw("DATE(created_at) AS date"),
             DB::raw("COUNT(CASE WHEN os_flash_result = 'PASS' THEN 1 END) AS pass_count"),
             DB::raw("COUNT(CASE WHEN os_flash_result = 'FAIL' THEN 1 END) AS fail_count"))
    ->where("created_at", ">=", "2018-09-17")
    ->groupBy("date")
    ->get();

这对应于以下原始mysql查询：

SELECT
    DATE(created_at) AS date,
    COUNT(CASE WHEN os_flash_result = 'PASS' THEN 1 END) AS pass_count,
    COUNT(CASE WHEN os_flash_result = 'FAIL' THEN 1 END) AS fail_count
FROM mobile_prod_pograms    -- should be called mobile_prod_programs
WHERE
    created_at >= '2018-09-17'
GROUP BY
    date;

赞(0）回复(0）举报 2021-06-20

我来回答

laravel：从mysql获取通过和失败结果的总数

1条答案

相关问题

热门标签

最新问答