在 group by 中使用 case 表達式時查詢的異常行為，失去了一些記錄

我有 4 個具有以下結構的表：

表“客戶資訊”

create table Customer_Info
(
 Customer_Number         int,
 Customer_Issued_Branch  int,
 <Other Columns related to customer>
)

範例數據：

 Insert into Customer_Info(Customer_Number,Customer_Issued_Branch)
 values (1,1001),(2,1002),(3,1003),(4,1001)

表“Customer_Transaction_Info”

create table Customer_Transaction_Info
(
 Customer_Number    int,
 Channel_Number     int,
 Transaction_Count  int,
 <Other columns related to customer transactions >
)

範例數據：

Insert into Customer_Transaction_Info(Customer_Number,Channel_Number,Transaction_Count)
Values(1,5,2),(1,6,1),(1,3,1),(1,1,1),(1,2,1),(2,5,1),(2,1,1)

表“Customer_Channel_Info”

create table Customer_Channel_Info
(
 Customer_Number         int,
 Channel_Number          int,
 Channel_Branch_code     int
)

範例數據：

insert into Customer_Channel_Info (Customer_Number,Channel_Number,Channel_Branch_code)
values (1,5,1001),(1,6,1002),(1,3,1003),(2,5,1004),(3,6,1005),(4,3,1006)

表“Branch_Info”

create table Branch_Info
(
 Branch_Code      int,
 New_Branch-Code  int
)

範例數據：

insert into Branch_Info(Branch_Code,New_Branch-Code)
values(1001,1010),(1002,1010),(1003,1010),(1004,1010),(1005,1010),(1006,1010)

問題是在我查詢的這一部分之前一切似乎都很好on t4.Branch_Code = t2.Channel_Branch_code。我的意思是，select *下面的查詢，without group by and sum()給了我 7 條記錄，但是只要我按部分添加分組和總和，下面的查詢就會返回 4 條記錄！它是有線的，我不明白原因！查詢的組部分和選擇部分中的兩個 case 語句完全相同，那麼為什麼會失去某些記錄？

我的查詢是：

select t1.Customer_Number,
      case
        when t1.Channel_Number in (1, 2) then t3.Customer_Issued_Branch
        else t4.New_Branch-Code
      end brncode,
      sum(case
            when t1.Channel_Number = 1 then t1.Transaction_Count
            else 0
          end) Tel_Count
 from Customer_Transaction_Info t1
 left join Customer_Channel_Info t2
   on t1.Customer_Number= t2.Customer_Number
  and t1.Channel_Number = t2.Channel_Number
 left join Customer_Info t3
   on t3.Customer_Number = t1.Customer_Number
 left join Branch_Info t4
   on t4.Branch_Code = t2.Channel_Branch_code
group by t1.customer_num,------------------------>/*This part*/
         case
           when t1.Channel_Number in (1, 2) then
            t3.cust_branch_cod
           else
            t4.New_Branch
         end

提前致謝

此行為與您的組直接相關 - 通過“唯一”您customer_num的 s（您的範例數據中有 4 個），它的行為符合預期。

如果沒有有關您希望通過此查詢獲得什麼的更多資訊，則很難確定您希望從此查詢中獲得什麼結果。如果您確實需要任何進一步的幫助，我很樂意提供任何指導。

請參閱https://www.w3schools.com/sql/sql_groupby.aspGROUP BY上的完整說明。

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