多表关联查询记录总计数显示异常问题求助

问题描述 #

需要解决多表关联查询的记录计数问题：基于Header表中File_Creation_Date在'220512'至'220514'之间的Transaction_ID，关联Entry、Addenda、Batch三张表查询记录，要求每条结果的TotalCount显示符合条件的总记录数（示例中为2），但当前查询的TotalCount每条仅为1。

表结构及示例数据 #

-- Entry表
ID, Transaction_ID, Dfi_Acct_Nbr,   Individual_Name
'1'    '108'    'test_Dfi_Acct_Nbr' 'test_Name'
'2'    '110'    '23456789'         'Test Name'
'3'    '113'    '099898'             'RK'

-- Addenda表
ID, Transaction_ID, Return_Description
'9'    '113'           'T Desc'
'10'    '108'           'test_Description'
'11'    '110'           '041152667'

-- Batch表
ID, Transaction_ID, Standard_Code
'5'    '108'            ''
'6'    '110'           'WEB'

-- Header表
ID, Transaction_ID, File_Creation_Date 
'15'   '115'            '220315'
'16'   '110'            '220513'
'18'   '113'            '220315'
'19'   '108'            '220514'

当前查询语句 #

SELECT COUNT(e.Transaction_ID) as TotalCount, 
   e.Dfi_Acct_Nbr,e.Individual_Name,a.Return_Description,h.Standard_Code      
FROM ENTRY e 
JOIN ADDENDA a ON e.Transaction_ID = a.Transaction_ID
JOIN BATCH h ON h.ransaction_ID = a.Transaction_ID -- 存在拼写错误：ransaction_ID 应为 Transaction_ID
WHERE e.Transaction_ID in (SELECT Transaction_Id 
  FROM HEADER WHERE File_Creation_Date BETWEEN '220512' AND '220514') 
GROUP BY  e.Dfi_Acct_Nbr,e.Individual_Name, a.Return_Description,  h.Standard_Code  

当前输出结果 #

TotalCount, Dfi_Acct_Nbr,    Individual_Name, Return_Description,  Standard_Code
'1',       'test_Dfi_Acct_Nbr','test_Name',  'test_Description',    ''
'1',       '23456789',        'Test Name',   '041152667',          'WEB'

期望输出结果 #

TotalCount, Dfi_Acct_Nbr,    Individual_Name, Return_Description,  Standard_Code
'2',       'test_Dfi_Acct_Nbr','test_Name',  'test_Description',    ''
'2',       '23456789',        'Test Name',   '041152667',          'WEB' 

修正后的查询语句 #

方法1：子查询预计算总记录数 #

SELECT 
    (SELECT COUNT(*) 
     FROM ENTRY e_sub 
     JOIN ADDENDA a_sub ON e_sub.Transaction_ID = a_sub.Transaction_ID
     JOIN BATCH h_sub ON h_sub.Transaction_ID = a_sub.Transaction_ID
     WHERE e_sub.Transaction_ID IN (SELECT Transaction_Id FROM HEADER WHERE File_Creation_Date BETWEEN '220512' AND '220514')) AS TotalCount,
    e.Dfi_Acct_Nbr,
    e.Individual_Name,
    a.Return_Description,
    h.Standard_Code
FROM ENTRY e 
JOIN ADDENDA a ON e.Transaction_ID = a.Transaction_ID
JOIN BATCH h ON h.Transaction_ID = a.Transaction_ID
WHERE e.Transaction_ID IN (SELECT Transaction_Id FROM HEADER WHERE File_Creation_Date BETWEEN '220512' AND '220514')

方法2：窗口函数直接计算总记录数 #

SELECT 
    COUNT(*) OVER() AS TotalCount,
    e.Dfi_Acct_Nbr,
    e.Individual_Name,
    a.Return_Description,
    h.Standard_Code
FROM ENTRY e 
JOIN ADDENDA a ON e.Transaction_ID = a.Transaction_ID
JOIN BATCH h ON h.Transaction_ID = a.Transaction_ID
WHERE e.Transaction_ID IN (SELECT Transaction_Id FROM HEADER WHERE File_Creation_Date BETWEEN '220512' AND '220514')

说明 #

1. 原查询问题：使用COUNT(e.Transaction_ID)并按每行唯一字段分组，导致每组仅统计到1条记录，因此TotalCount为1。
2. 方法1通过子查询预先计算符合条件的总记录数，将其作为常量列返回给每一行。
3. 方法2使用窗口函数COUNT() OVER()，直接计算整个结果集的总记录数，无需分组即可将总数值赋给每一行。
4. 同时修复了原查询中Batch表关联条件的拼写错误：h.ransaction_ID改为h.Transaction_ID。

内容的提问来源于stack exchange，提问作者RKN