sql - 根据案例条件计算一列

Question

这是代码：

select  sum(case when status_code= '200' then 1 else 0 end) as success,
      sum(case when status_code!= '200' then 1 else 0 end ) as failure from my_table 

输出：

success  failure    
  1          2   

仅当失败计数 < = 5 时，我才必须选择计数失败

标签： sqlsql-server

Answer 1

解决方案

添加有子句。

select sum(case when status_code= '200' then 1 else 0 end) as 'success',
       sum(case when status_code!= '200' then 1 else 0 end) as 'failure'
from my_table
having sum(case when status_code!= '200' then 1 else 0 end) <= 5;

---

完整示例

小提琴

结果：

declare @my_table table
(
    status_code int
);

insert into @my_table (status_code) values
(200), (1), (1); --> 2 failures

select  sum(case when status_code  = 200 then 1 else 0 end) as 'success',
        sum(case when status_code != 200 then 1 else 0 end) as 'failure'
from @my_table
having  sum(case when status_code != 200 then 1 else 0 end) <= 5;


-- RESULT (1 row)
success     failure
----------- -----------
1           2

没有结果：

insert into @my_table (status_code) values
(1), (1), (1), (1); --> +4 failures ==> 6 failures in total

select  sum(case when status_code  = 200 then 1 else 0 end) as 'success',
        sum(case when status_code != 200 then 1 else 0 end) as 'failure'
from @my_table
having  sum(case when status_code != 200 then 1 else 0 end) <= 5;


-- RESULT (0 rows)