使用CASE和分组依据的SQL查询

本文介绍了使用CASE和分组依据的SQL查询的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个功能表，其中每个功能都由其ID(数据库列)标识，而错误表中的每个功能与错误表之间存在一对多的关系.

I have a Feature Table where each feature is identified by its ID(DB column) and Bugs table where each feature has one to many relation ship with bugs table.

Feature Table has columns id Description Bugs Table has columns ID Feature_ID Status

如果错误的状态为0或1，则将其视为打开；如果状态为2，则将其视为已关闭.

I will consider a bug as opened if its state is either 0 or 1 and as closed if Status is 2.

我正在尝试编写一个查询，该查询指示基于功能的状态可以将其视为通过或失败.

I am trying write a query which indicates whether a Feature can be considered as passed or failed based on it's Status.

select F.ID CASE WHEN count(B.ID) > 0 THEN 'FAIL' ELSE 'PASS' END as FEATURE_STATUS from Feature F, Bugs B where B.Status in (0,1) group by F.ID;

我的查询始终提供失败的功能，但未通过，如何修改我的查询以同时返回两者?

My query always gives the Failed Features but not passed, how can modify my query to return both?

推荐答案

SELECT F.ID, CASE WHEN SUM(CASE WHEN B.ID IN (0, 1) THEN 1 ELSE 0 END) > 0 THEN 'Fail' ELSE 'Success' END AS FEATURE_STATUS from Feature F JOIN Bugs B ON B.Feature_ID = F.ID group by F.ID