Sql-Server

使用 Group By 時，完全外連接顯示 NULL

  • June 10, 2016

我有 4 個子查詢，每個子查詢都按“組名”分組。嘗試將每個子查詢作為一列全部按“組名”分組。這是查詢：

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, cr.type
       ) 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, cr.type
   ) 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, cr.type
   ) 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)

但同樣的結果。

程式碼中是否存在明顯缺陷？我最好的估計是查看合併函式 - …

兩者都是。一個問題是第二個（和第三個）FULL JOIN使用（派生）表的group_name列。結果，第 2 次和第 3 次完全連接分別取消了第 1 次和第 2 次完全連接。您的查詢僅具有基本結構，從細節中剝離：

select  
   coalesce(co.group_name, requests.group_Name, incidents.group_Name, 
            problems.group_Name) as 'SD Groups'
   ---
from
   ( --- ) as CO
full outer join 
   ( --- ) as Requests
   on co.group_name = requests.group_name
full outer join
   ( --- ) as Problems
   on  requests.group_name = problems.group_name
full outer join 
   ( --- ) as Incidents
   on requests.group_name = incidents.group_name

第一次完全加入後：

ON co.group_name = requests.group_name

可能有來自任一表（groups或requests）的行與另一個不匹配，並且空列填充有NULL值。

然後發生第二次完全連接：

ON requests.group_name = problems.group_name

但是ON只有具有 non-null 的行才能滿足此條件requests.group_name。這基本上將這 2 個FULL連接轉換為一個複雜的混亂，幾乎（但不完全）是 3 個表的完全連接。某些行將不匹配，特別是表中的行co並且problems具有相同的group_name行（但 table 中不存在此類行requests）將不匹配，但最終會出現在不同的行中。

再次第三次完全加入：

ON requests.group_name = incidents.group_name

會讓事情變得更加複雜。因此，您的查詢相當於（requests左連接到其他 3 個表）和 3 個反連接（3 個表中的每一個）的並集requests：

select  
   ---
from
   ( --- ) as Requests
left outer join
   ( --- ) as CO
   on co.group_name = requests.group_name
left outer join
   ( --- ) as Problems
   on  requests.group_name = problems.group_name
left outer join 
   ( --- ) as Incidents
   on requests.group_name = incidents.group_name

union all 

select  
   ---
from
   ( --- ) as CO
left outer join
   ( --- ) as Requests
   on co.group_name = requests.group_name
where 
   requests.group_name is null

union all 

select  
   ---
from
   ( --- ) as Problems
left outer join
   ( --- ) as Requests
   on problems.group_name = requests.group_name
where 
   requests.group_name is null

union all 

select  
   ---
from
   ( --- ) as Incidents
left outer join
   ( --- ) as Requests
   on incidents.group_name = requests.group_name
where 
   requests.group_name is null

這就是相同內容group_name出現在兩行中的原因。您可以看到，僅當CO沒有具有此類值的行但其他表具有時才會發生這種情況。

如果您真的想擁有 3 個FULL連接，ON則應使用ISNULLor重寫條件COALESCE：

select  
   coalesce(co.group_name, requests.group_Name, incidents.group_Name, 
            problems.group_Name) as [SD Groups]
   ---
from
   ( ---
   ) as CO
full outer join 
   ( ---
   ) as Requests
   on co.group_name = requests.group_name
full outer join
   ( ---
   ) as Problems
   on coalesce(co.group_name, requests.group_name) 
      = problems.group_name
full outer join 
   ( ---
   ) as Incidents
   on coalesce(co.group_name, requests.group_name, problems_group_name) 
      = incidents.group_name
order by 
   --- ;

其他小問題是'single quotes'別名的使用。您應該使用標准"double quotes"或 SQL Server 的[square brackets]. 或者更好的是不要引用您的標識符並保持它們沒有空格和其他奇怪的字元。單引號字元串應該只用於字元串文字，而不是標識符。

查詢中的最後三個派生表幾乎相同：它們以相同的方式連接相同的表並使用幾乎相同的過濾器。唯一的區別是cr.type條件：每個子查詢過濾該列的不同值。

在這種情況下，您可以輕鬆地將這三個派生表合併為一個生成所有三個計數的表。您只需要為此使用條件聚合：

(
select                                 
   groups.last_name AS Group_Name   
   ,count(CASE cr.type WHEN 'R' THEN ref_num END) AS Requests
   ,count(CASE cr.type WHEN 'P' THEN ref_num END) AS Problems
   ,count(CASE cr.type WHEN 'I' THEN ref_num END) 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 IN ('R', 'P', 'I')  -- probably redundant if cr.type cannot have other values
   and     cc.company_name = 'XYZ'                 
group by    groups.last_name
) as CR

如您所見，特定cr.type過濾器現在以 CASE 表達式的形式移動到聚合函式中。這樣每個 COUNT 只考慮具有指定的行cr.type。還要注意cr.typeGROUP BY 中沒有。

為了完整起見，這也是您的主要 SELECT 將如何更改：

SELECT
   coalesce(CO.group_name, CR.group_Name) AS [SD Groups],
   , isnull(CO.co,        '') as CO
   , isnull(CR.incidents, '') as Inc
   , isnull(CR.problems,  '') as Prob
   , isnull(CR.requests,  '') as Rqst
FROM
   ...

鑑於您的查詢的複雜性，我希望這種重寫能夠顯著提高查詢的性能。此外，由於要加入的數據集數量現在減少到只有兩個，因此您的原始問題（其實質內容已在ypercubeᵀᴹ的答案中進行了解釋）也可能會消失。

引用自：https://dba.stackexchange.com/questions/140876

相關問答

Sql-Server

如何在 SQL Server 中使用 SUM 和 GROUP BY 連接兩個表

  • April 29, 2022
檢視問答
Sql-Server

如何獲得計數為零的組？

  • January 12, 2022
檢視問答
Sql-Server

將三個表分組為一個主表和兩個詳細表

  • September 9, 2021
檢視問答
Sql-Server

SQL 連接查詢以顯示一個表中不存在的行

  • May 8, 2020
檢視問答
Sql-Server

JOIN/Union 兩個表，盡可能去掉NULL（類似於pandas concatenate）

  • May 5, 2020
檢視問答
Sql-Server

即使計數為0，如何按週分組

  • April 29, 2019
檢視問答