加入语法有问题！ ！ [英] Problem with the syntax of join! !

问题描述

大家好

i有以下查询需要加入JOB_NAME关键字段： -

查询1： -

 选择 JOB_NAME，STATUS，Count（JOB_NAME） as  Fail_Count 来自 COMP_HIS_TBL A 其中​​ STATUS = ' 失败' 组 通过 JOB_NAME，状态

查询2： -

 选择 JOB_NAME，STATUS，Count（JOB_NAME）  OverallCount 来自 COMP_HIS_TBL 
 组 按 JOB_NAME，状态

Fail_Count和Over allCount都是自定义的添加字段。需要加入以上两个表，使用以下连接语句： -



我尝试过：

 选择 A.JOB_NAME，A.STATUS，将（A.JOB_NAME）计为 Fail_Count 来自 COMP_HIS_TBL A  JOIN  
（选择 JOB_NAME，状态，计数（JOB_NAME）为 OverallCount 来自 COMP_HIS_TBL 
 组 按 JOB_NAME，状态）B 
 开启 
 A.JOB_NAME = B.JOB_NAME 
 其中 A.STATUS = ' 失败' 
 组 按 A.JOB_NAME，A.STATUS 

但是输出中缺少OverallCount？需要帮助：（

解决方案

< blockquote>在这种情况下，你甚至不需要 JOIN ：

  SELECT  
 JOB_NAME，
 SUM（ CASE 状态 WHEN  ' 失败' 那么  1   ELSE   0  结束）作为 Fail_Count，
 COUNT（JOB_NAME）作为 OverallCount 
  FROM  
 COMP_HIS_TBL 
  GROUP   BY  
 JOB_NAME 
; 

Hi All
i have the following queries which needs to be joined on JOB_NAME key field:-
Query1:-

Select JOB_NAME,STATUS,Count(JOB_NAME) as Fail_Count from COMP_HIS_TBL A where STATUS = 'Failure' Group By JOB_NAME,STATUS

Query2:-

Select JOB_NAME,STATUS,Count(JOB_NAME) as OverallCount from COMP_HIS_TBL 
Group By JOB_NAME,STATUS

Both Fail_Count and OverallCount are the custom added fields.Need to join the above two tables,used the following join statement:-

What I have tried:

Select A.JOB_NAME,A.STATUS,Count(A.JOB_NAME) as Fail_Count from COMP_HIS_TBL A  JOIN
(Select JOB_NAME,STATUS,Count(JOB_NAME) as OverallCount from COMP_HIS_TBL 
Group By JOB_NAME,STATUS) B
On 
A.JOB_NAME=B.JOB_NAME
Where A.STATUS = 'Failure'
Group By A.JOB_NAME,A.STATUS 

However the OverallCount is missing from my output?Need help regarding this:(

解决方案

In this case, you don't even need a JOIN:

SELECT
    JOB_NAME,
    SUM(CASE Status WHEN 'Failure' THEN 1 ELSE 0 END) As Fail_Count,
    COUNT(JOB_NAME) As OverallCount
FROM
    COMP_HIS_TBL
GROUP BY
    JOB_NAME
;

这篇关于加入语法有问题！ ！的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！