Question

我试图计算所有数据组有一个或多个故障的情况。

我也认为,很难解决这一问题,因此,我ping希望,在解释我想要达到的目标时,举一个例子。

抽样数据:

INSERT INTO test.answers (id, result_id, fail_all, fail_group) VALUES
    (1,1,0,1),  (2,1,0,1),  (3,1,0,1),  (4,1,0,0),
    (5,2,1,0),  (6,2,0,0),  (7,2,1,0),  (8,2,1,0),  (9,2,1,0),
    (10,3,0,1), (11,3,1,1), (12,3,0,1), (13,3,0,1), (14,3,0,1),
    (15,4,0,0), (16,4,0,0), (17,4,0,1), (18,4,0,1), (19,4,0,0), (20,4,0,1),
    (21,5,1,0), (22,5,0,1), (23,5,1,1), (24,5,0,1), (25,5,1,0), (26,5,0,1);

INSERT INTO test.results (id,team_id) VALUES
    (1,1), (2,1), (3,1), (4,2), (5,2);

接着,我提出以下问题:

SELECT
    COUNT(IF(a.fail_all = 1,1,NULL)) AS count_fail_all,
    COUNT(IF(a.fail_group = 1,1,NULL)) AS count_fail_group,
    a.result_id
FROM test.answers AS a
GROUP BY a.result_id

结果:

count_fail_all, count_fail_group, result_id
0, 3, 1
4, 0, 2
1, 5, 3
0, 3, 4
3, 4, 5

我需要提出一个问题,即由团队组成的团体,并估计有多少失败。如果结果不止一次失败,那么总体结果就失败了。因此,如果上述查询中的成果数为3(例:初步结果),则只能算作1。失败的集团现在可以被忽略,因为我认为失败的同样解决办法——都会为失败而努力。

我希望其结果是:

team_id, amount_of_fails, amount_of_fails_per_group
1, 2, 2
2, 1, 2

I hope someone might be able to help me create the query that I need? I m not even sure how to start. Let me know if there is anything that I can do to adjust the query as I know it s not very well asked?

Thanks!

Answer 1

如果你想要这一产出的话。或许可以这样说:

SELECT
  r.team_id,
  SUM(IF(a.fail_all = 1,1,0)) AS amount_fail_all,
  SUM(IF(a.fail_group = 1,1,0)) AS amount_fail_group
 FROM answers AS a
 JOIN results AS r on r.id=a.result_id
 GROUP BY r.team_id

这将使你获得这一产出:

team_id  amount_fail_all  amount_fail_group
1        2                2
2        1                2

友情链接