php - 我如何选择数据和子查询组

Question

当我选择数据时，只显示第一个lot数据。但我需要所有lot数据。这是我的查询：

SELECT lot,
(select count(pass) FROM pass_fail_result where pass=0) toatl_fail,
(select count(pass) FROM pass_fail_result where pass=1) toatl_pass FROM pass_fail_result group by lot;

我想显示所有通过结果pass=10，例如fail=2

标签： phpcodeigniter

Answer 1

最简单的方法是通过条件聚合，我们计算或求和CASE针对失败或通过记录的表达式：

SELECT
    lot,
    COUNT(CASE WHEN pass = 0 THEN 1 END) AS toatl_fail,
    COUNT(CASE WHEN pass = 1 THEN 1 END) AS toatl_pass
FROM pass_fail_result
GROUP BY
    lot;