使用CASE和分组依据的SQL查询 [英] SQL Query with CASE and group by

问题描述

我有一个功能表，其中每个功能都由其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

这篇关于使用CASE和分组依据的SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！