总结每个组的SQL查询
总结每个组的SQL查询
提问于 2021-08-25 16:21:36
我需要帮忙处理这个。我需要总结每组测试。结果应该如下所示：
name_of_the_group | all_test_cases | passed_test_cases | total_value
numerical stability 4 4 80
memory usage 3 2 20
corner cases 0 0 0
performance 2 0 0栏：
- name_of_the_group (所有组都需要显示，即使没有相应的测试)
- all_test_cases (组中的测试次数)
- passed_test_cases (状态确定的测试用例数)
- total_value (这个组通过的测试的总价值)
下面是小提琴示例：http://sqlfiddle.com/#!17/e96109
回答 6
Stack Overflow用户
回答已采纳
发布于 2021-08-25 16:25:50
简单的条件聚合：
select
t.name,
count(*) as total,
sum(case when c.status = 'OK' then 1 else 0 end) as passed,
sum(case when c.status = 'OK' then test_value end) as score
from
test_groups t
left join test_cases c
on t.name = c.group_name
group by t.name小提琴
Stack Overflow用户
发布于 2021-12-04 13:51:28
下面的SQL查询提供了所有测试用例的解决方案。
- 当表test_groups中有一个名称条目但在test_cases中缺少时。
- 顺序由test_value先排序，然后按名称按字母顺序递增。
SELECT CASE
WHEN g.name = c.group_name THEN c.group_name
ELSE g.name
END AS name,
count(c.group_name) AS all_test_cases,
count(CASE
WHEN c.status = 'OK' THEN 1
END) AS all_test_cases,
sum(CASE
WHEN c.status = 'OK' THEN g.test_value
ELSE 0
END) AS total_value
FROM test_groups g
LEFT JOIN test_cases c ON g.name = c.group_name
GROUP BY CASE
WHEN g.name = c.group_name THEN c.group_name
ELSE g.name
END
ORDER BY total_value DESC,
CASE
WHEN g.name = c.group_name THEN c.group_name
ELSE g.name
ENDStack Overflow用户
发布于 2021-12-24 07:17:45
with all_test_cases as (select name, count(id) as cnt from (
select a.name, a.test_value, b.id, b.status from test_groups a
left join test_cases b on a.name = b.group_name) total_test
group by name),
passed_test_cases as (
select name, count(name) as cnt from (
select a.name, a.test_value, b.id, b.status from test_groups a
join test_cases b on a.name = b.group_name
and b.status = 'OK') OK_test
group by name)
select tg.name, atc.cnt as all_test_cases , coalesce(ptc.cnt, 0) as passed_test_cases, coalesce(ptc.cnt * tg.test_value, 0) as total_value
from test_groups tg
left join all_test_cases atc on tg.name = atc.name
left join passed_test_cases ptc on tg.name = ptc.name
order by total_value desc, name页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/68926568
复制相关文章
相似问题
腾讯云开发者
