使用group时出现的完全外部联接问题

Question

@@这可能看起来是重复的。另一篇文章以“来宾”的形式发布，不允许我做编辑。@@

我有4个子查询，每个子查询都按‘grouped’分组。尝试将每个子查询作为一个列，所有列都按‘grouped’分组。以下是查询：

select  coalesce(co.group_name, requests.group_Name, incidents.group_Name, problems.group_Name) as 'SD Groups'
, isnull(co.co, '') as 'CO'
, isnull(incidents.incidents, '' ) as 'Inc'
, isnull(problems.problems, '') as 'Prob'
, isnull(requests.requests, '') as 'Rqst'

from
(select 
    groups.last_name                        AS Group_Name   
    ,count(chg_ref_num)                         AS 'CO'
from chg
left join   ca_contact groups on chg.group_id = groups.contact_uuid 
left join   ca_contact assignee on chg.assignee = assignee.contact_uuid 
left join   ca_company cc on  assignee.company_uuid = cc.company_uuid                       
where   groups.last_name in ('8197 Qlikview Support', '8202 OBIEE-BIP'
                        , '8205 BI SAS', '8206 BI CCBI', '8208 BI ePlan Reporting and Support'
                        , '8211 BI Data Quality', '8212 BI EPM Report Architect/Developer'
                        , '8213 BI EPM Framework Architect/Developer', '8214 BI EPM User Experience'
                        , '8215 BI EPM OLAP Architect/Developer', '8219 BI Data Warehouse') 
and status = 'CL'
and     convert(varchar, dateadd(hh,-8,dateadd(ss,chg.close_date, '1970')), 101)  >= DATEADD(month, datediff(month, 0, getdate()), 0)
and     parent is NULL  
and     cc.company_name = 'XYZ' 
group by    groups.last_name) as CO

full outer join 
(select                         
groups.last_name                        AS Group_Name   
    ,count(ref_num)                         AS Requests

from call_req cr                                
left join   ca_contact groups on cr.group_id = groups.contact_uuid  
left join   ca_contact assignee on cr.assignee = assignee.contact_uuid  
left join   ca_company cc on  assignee.company_uuid = cc.company_uuid                       
where   groups.last_name in ('8197 Qlikview Support', '8202 OBIEE-BIP'
                        , '8205 BI SAS', '8206 BI CCBI', '8208 BI ePlan Reporting and Support'
                        , '8211 BI Data Quality', '8212 BI EPM Report Architect/Developer'
                        , '8213 BI EPM Framework Architect/Developer', '8214 BI EPM User Experience'
                        , '8215 BI EPM OLAP Architect/Developer', '8219 BI Data Warehouse')                         
and     cr.status in ('CL')     
and     convert(varchar, dateadd(hh,-8,dateadd(ss,cr.close_date, '1970')), 101)  >= DATEADD(month, datediff(month, 0, getdate()), 0)                    
and     cr.parent is NULL   
and     cr.type = 'R'   
and     cc.company_name = 'XYZ'                 
group by    groups.last_name) as Requests
on co.group_name = requests.group_name

full outer join
(select                                 
groups.last_name                        AS Group_Name   
    ,count(ref_num)                     AS Problems

from call_req cr                                
left join   ca_contact groups on cr.group_id = groups.contact_uuid
left join   ca_contact assignee on cr.assignee = assignee.contact_uuid  
left join   ca_company cc on  assignee.company_uuid = cc.company_uuid                               
where   groups.last_name in ('8197 Qlikview Support', '8202 OBIEE-BIP'
                        , '8205 BI SAS', '8206 BI CCBI', '8208 BI ePlan Reporting and Support'
                        , '8211 BI Data Quality', '8212 BI EPM Report Architect/Developer'
                        , '8213 BI EPM Framework Architect/Developer', '8214 BI EPM User Experience'
                        , '8215 BI EPM OLAP Architect/Developer', '8219 BI Data Warehouse')                         
and     cr.status in ('CL')     
and     convert(varchar, dateadd(hh,-8,dateadd(ss,cr.close_date, '1970')), 101)  >= DATEADD(month, datediff(month, 0, getdate()), 0)                    
and     cr.parent is NULL   
and     cr.type = 'P'   
and     cc.company_name = 'XYZ'             
group by    groups.last_name) as Problems
on  requests.group_name = problems.group_name

full outer join 
(select                                 
groups.last_name                        AS Group_Name   
    ,count(ref_num)                         AS Incidents

from call_req cr                                
left join   ca_contact groups on cr.group_id = groups.contact_uuid  
left join   ca_contact assignee on cr.assignee = assignee.contact_uuid  
left join   ca_company cc on  assignee.company_uuid = cc.company_uuid                           
where   groups.last_name in ('8197 Qlikview Support', '8202 OBIEE-BIP'
                        , '8205 BI SAS', '8206 BI CCBI', '8208 BI ePlan Reporting and Support'
                        , '8211 BI Data Quality', '8212 BI EPM Report Architect/Developer'
                        , '8213 BI EPM Framework Architect/Developer', '8214 BI EPM User Experience'
                        , '8215 BI EPM OLAP Architect/Developer', '8219 BI Data Warehouse')                         
and     cr.status in ('CL')     
and     convert(varchar, dateadd(hh,-8,dateadd(ss,cr.close_date, '1970')), 101)  >= DATEADD(month, datediff(month, 0, getdate()), 0)                    
and     cr.parent is NULL   
and     cr.type = 'I'   
and     cc.company_name = 'XYZ'                 
group by    groups.last_name) as Incidents
on requests.group_name = incidents.group_name

order by 'SD Groups' asc

以下是研究结果：

Group_Name                                     CO  Inc Prob Rqst
8197 Qlikview Support                           0   1   0   7
8202 OBIEE-BIP                                  0   4   0   11
8205 BI SAS                                     0   11  1   11
8206 BI CCBI                                   10   17  0   43
8208 BI ePlan Reporting and Support             0   0   0   4
8211 BI Data Quality                            0   0   0   12
8212 BI EPM Report Architect/Developer          0   3   1   5
8214 BI EPM User Experience                     0   2   0   0
8214 BI EPM User Experience                     0   0   1   0
8215 BI EPM OLAP Architect/Developer            0   15  0   2
8219 BI Data Warehouse                         16   71  4   13

注意组8214的两行。每一组应代表一次，第8214组的预期结果应为：

Group_Name                                     CO  Inc Prob Rqst
8214 BI EPM User Experience                     0   2   1   0

我做错了什么？我认为这与合并功能有关。我尝试了其他方法，包括

isnull(isnull(isnull(co.group_name, requests.group_Name), incidents.group_Name), problems.group_Name) 

但同样的坏结果。

Answer 1

这只是根据您发布的代码在黑暗中拍摄的一张照片，但我注意到您的连接不一致。

(按groups.last_name分组)作为co.group_name = requests.group_name上的请求

Groups.last_name的群)作为requests.group_name = problems.group_name上的问题

( groups.last_name)在requests.group_name = incidents.group_name上发生的事件

我认为它为8214创建了一个副本，因为其中一个内联select语句中的结果集，但是我必须访问数据才能确定。

至少，我建议您用"co.group_name = new.group_name“来格式化每个‘联接’子句

这也可能是因为你的完全外部连接，我不确定你是否需要。我确信完全连接将为每个查询中遇到的每个组提供一行，并合并结果，但这是您的工作。