我如何排除0结果 [英] How I can exclude the 0 results

问题描述

我有这个查询

选择
 a.Des作为'Alm'，
 SUM（案例k .IdE当1然后1其他0结束）正常，
 SUM（情况k.IdE当2然后1其他0结束）as Urgent 
 FROM TblKBA a 
 left join TblKBK k $ b a.Des上的$ b = k.IdAr 
组a.Des 

此结果

 Alm正常紧急
 U1.K1.WH1 6 5 
 U1.K1.WH2 9 9 
 U1.K2。 WH3 0 0 
 U2.K3.WH4 0 0 

i想要在正常或紧急时隐藏alm行为0，在这个例子中我想隐藏最后两行。



i想得到这个结果

 Alm正常紧急
 U1.K1.WH1 6 5 
 U1.K1.WH2 9 9 

我怎么能这样做？



问候



我尝试过：



i试图结合选择，拥有，在哪里，排除......

解决方案

最简单的要做到这一点，将包装你在公用表表达式（CTE）中然后查询CTE。

满足正常或紧急是0 要求，我使用的WHERE子句只是乘以列，因为任何乘以0的都是0.

;  WITH  cte（Alm，Normal，Urgent）
  AS （
  SELECT  a.Des  as  '  Alm' 
，SUM（ case  k.IdE 何时  1  然后  1   else   0   end ） as 正常
，SUM（案例 k.IdE 何时  2     1   else   0   end ） as 紧急
  FROM  TblKBA a 
  LEFT  < span class =code-keyword> JOIN  TblKBK k  ON  a.idA = k.IdAr 
  GROUP   BY  a.Des 
）
 
  SELECT  * 
  FROM  cte 
  WHERE （（正常*紧急）> ;  0 ）

如果要过滤掉正常的行和紧急都是零，那么你可以在分组之前这样做：

  SELECT  
 a.Des  as  '   Alm'，
 SUM（ CASE  k.IdE  WHEN   1  那么  1   ELSE   0   END  ） As 正常，
 SUM（ CASE  k.IdE  WHEN   2  那么  1   ELSE   0   END ） As 紧急
  FROM  
 TblKBA a 
  INNER   JOIN  TblKBK k 
  ON  a.idA = k.IdAr 
  WHERE  
 k.IdE 在（ 1 ，< span class =code-digit> 2 ）
  GROUP   BY  
 a.Des 
; 

如果你想过滤掉正常的行或者紧急为零，然后您可以使用 HAVING 子句：

  SELECT  
 a.Des  as  '  Alm'，
 SUM（ CASE  k.IdE  WHEN   1  那么  1   ELSE   0   END ）正如正常，
 SUM（ CASE  k.IdE  WHEN   2  那么  1   ELSE   0   END ）作为紧急
  FROM  
 TblKBA a 
  INNER   JOIN  TblKBK k 
  ON  a.idA = k.IdAr 
  WHERE  
 k.IdE <跨度cl ass =code-keyword>在（ 1 ， 2 ）
  GROUP   BY  
 a.Des 
  HAVING  
 SUM（ CASE  k.IdE  WHEN   1  那么  1   ELSE   0   END ）！=  0  
 和 
 SUM（ CASE  k.IdE  WHEN   2  那么  1   ELSE   0   END ）！=  0  
; 

HAVING （Transact-SQL） - SQL Server | Microsoft Docs [ ^ ]

从
中选择*（选择
 a.Des作为'Alm'，
 SUM（情况k.IdE当1然后1其他0结束）为正常，
 SUM（情况k.IdE当2然后1其他0结束）as Urgent 
 FROM TblKBA a 
左边加入TblKBK k 
 a.idA = k.IdAr 
 group by a.Des）A 
 where（A.Normal> 0和A.Urgent> 0）

I have this query

select 
a.Des as 'Alm', 
SUM (case k.IdE when 1 then 1 else 0 end) as Normal, 
SUM (case  k.IdE when 2 then 1 else 0 end) as Urgent  
FROM TblKBA a 
left join TblKBK k 
on a.idA = k.IdAr
group by a.Des 

with this result

Alm	Normal	Urgent
U1.K1.WH1	6	5
U1.K1.WH2	9	9
U1.K2.WH3	0	0
U2.K3.WH4	0	0

i want to hide the alm row when normal or urgent are 0, in this example i want to hide the last two rows.

i want to get this result

Alm	Normal	Urgent
U1.K1.WH1	6	5
U1.K1.WH2	9	9

How i could do that?

regards

What I have tried:

i tryed to combine selects, having, where, exclude...

解决方案

Easiest way to do this would be wrap what you have in a Common Table Expression (CTE) and then query the CTE.
To meet the normal or urgent are 0 requirement, the WHERE clause I used simply multiplied the columns because anything multiplied by 0 is 0.

; WITH cte (Alm, Normal, Urgent)
AS (
     SELECT     a.Des as 'Alm'
          ,     SUM (case k.IdE when 1 then 1 else 0 end) as Normal
          ,     SUM (case  k.IdE when 2 then 1 else 0 end) as Urgent
     FROM      TblKBA  a 
     LEFT JOIN TblKBK  k ON a.idA = k.IdAr
     GROUP BY  a.Des
)

SELECT *
FROM   cte
WHERE  ((Normal * Urgent) > 0)

If you want to filter out the rows where Normal and Urgent are both zero, then you can do that before you group them:

SELECT
    a.Des as 'Alm', 
    SUM(CASE k.IdE WHEN 1 THEN 1 ELSE 0 END) As Normal, 
    SUM(CASE k.IdE WHEN 2 THEN 1 ELSE 0 END) As Urgent  
FROM 
    TblKBA a 
    INNER JOIN TblKBK k 
    ON a.idA = k.IdAr
WHERE
    k.IdE In (1, 2)
GROUP BY
    a.Des
;

If you want to filter out rows where either Normal or Urgent is zero, then you can use the HAVING clause:

SELECT
    a.Des as 'Alm', 
    SUM(CASE k.IdE WHEN 1 THEN 1 ELSE 0 END) As Normal, 
    SUM(CASE k.IdE WHEN 2 THEN 1 ELSE 0 END) As Urgent  
FROM 
    TblKBA a 
    INNER JOIN TblKBK k 
    ON a.idA = k.IdAr
WHERE
    k.IdE In (1, 2)
GROUP BY
    a.Des
HAVING
    SUM(CASE k.IdE WHEN 1 THEN 1 ELSE 0 END) != 0
And
    SUM(CASE k.IdE WHEN 2 THEN 1 ELSE 0 END) != 0
;

HAVING (Transact-SQL) - SQL Server | Microsoft Docs[^]

select * from 
(select 
a.Des as 'Alm', 
SUM (case k.IdE when 1 then 1 else 0 end) as Normal, 
SUM (case  k.IdE when 2 then 1 else 0 end) as Urgent  
FROM TblKBA a 
left join TblKBK k 
on a.idA = k.IdAr
group by a.Des )A
where (A.Normal>0 and A.Urgent>0)

这篇关于我如何排除0结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！