时间戳

首页 > 解决方案 > 如何在单个查询中获得零件总数、通过零件数和失败零件数

mysql - 如何在单个查询中获得零件总数、通过零件数和失败零件数

问题描述

有一个表格，其中包含数量、插入日期和结果列。

Quantity || TIMESTAMP || RESULT
Sample 1 || 2019-01-28 18:21:04 || PASS
Sample 2 || 2019-01-30 18:21:04 || FAIL

等等

单个查询如何仅给出零件总数、通过计数和失败计数 GROUPED BY 日期？

标签： mysql

解决方案

如果您使用的是 MySQL 5.x，那么使用多个子查询将是您的唯一选择。

select (select count(*) from Test ) as Total, passed.val, failed.val
from (select count(*) as val, tmstamp from Test where result = 'PASS' group by tmstamp) passed
   , (select count(*) as val, tmstamp from Test where result = 'FAIL' group by tmstamp) failed
WHERE passed.tmstamp = failed.tmstamp;

如果您使用的是 MySQL8，您应该研究 CTE（通用表表达式）功能。

WITH Passed as (select count(*) as val, tmstamp from Test where result = 'PASS' group by tmstamp),
Failed as (select count(*) as val, tmstamp from Test where result = 'FAIL' group by tmstamp)
select (select count(*) from Test ) as Total, p.val, f.val
From Passed p
     join Failed f on f.tmstamp = p.tmstamp

推荐阅读