带DISTINCT的多表关联查询中COUNT(*)统计行数与导出行数、Count Rows函数结果不一致的原因咨询

I’ve run into this exact kind of discrepancy before, so let’s break down what’s going on here and clear up your confusion.

Core Reason for the Count Difference #

Your original query uses SELECT DISTINCT to pull unique combinations of student/school/department/etc. names. When you replaced those columns with COUNT(*), you’re counting every row that matches your WHERE clause—including duplicate rows created by your multiple LEFT JOINs.

Here’s a simple way to visualize it: Suppose one student is linked to 2 different records in IEU01_SPECIALZTNAR. Your LEFT JOIN will duplicate that student’s row once for each matching speciality record. COUNT(*) will count both rows, but SELECT DISTINCT will collapse them into one row if all the selected column values are identical across those duplicates. That’s why your COUNT(*) gives 221077, but the exported DISTINCT result (and your tool’s Count Rows) shows 220516—you’re comparing apples (all joined rows) to oranges (unique column combinations).

Is Your Query Correct? #

Your query logic is totally valid for what it’s doing:

• It starts with C_BPARTNER and uses LEFT JOINs to pull in related student, college, division, speciality, and university data. This ensures you don’t lose partner records even if they don’t have linked entries in those other tables.
• The WHERE clause correctly filters for members (IEU01_ISMEMBER = 'Y').

The only "gotcha" is that multiple LEFT JOINs can create duplicate rows when a single parent record has multiple matches in a child table—this is expected behavior for JOINs, not a bug in your query.

How to Verify This #

To confirm this is the issue, run this query to count the exact number of unique rows from your original DISTINCT result:

SELECT COUNT(*) 
FROM (
    SELECT DISTINCT C_BPARTNER.NAME, IEU01_DIVISIONAR.NAME, IEU01_SPECIALZTNAR.NAME, IEU01_UNIMASTER_TRL.NAME, IEU01_COLLEGE_TRL.NAME 
    FROM C_BPARTNER 
    LEFT OUTER JOIN IEU01_STUDENTMASTER ON C_BPARTNER.C_BPARTNER_ID = IEU01_STUDENTMASTER.C_BPARTNER_ID 
    LEFT OUTER JOIN REPIEULIVE.IEU01_COLLEGE_TRL ON IEU01_STUDENTMASTER.IEU01_COLLEGE_ID = IEU01_COLLEGE_TRL.IEU01_COLLEGE_ID 
    LEFT OUTER JOIN IEU01_DIVISIONAR ON IEU01_STUDENTMASTER.IEU01_DIVISIONAR_ID = IEU01_DIVISIONAR.IEU01_DIVISIONAR_ID 
    LEFT OUTER JOIN REPIEULIVE.IEU01_SPECIALZTNAR ON IEU01_STUDENTMASTER.IEU01_SPECIALZTNAR_ID = IEU01_SPECIALZTNAR.IEU01_SPECIALZTNAR_ID 
    LEFT OUTER JOIN REPIEULIVE.IEU01_UNIMASTER_TRL ON IEU01_STUDENTMASTER.IEU01_UNIMASTER_ID = IEU01_UNIMASTER_TRL.IEU01_UNIMASTER_ID 
    WHERE C_BPARTNER.IEU01_ISMEMBER = 'Y'
) AS distinct_rows;

This should return exactly 220516, matching your export and Count Rows result.

If you want to find which JOIN is causing the duplicates, try removing one JOIN at a time and re-running COUNT(*). The JOIN that causes the biggest jump in row count is the one creating the most duplicates.

Why the Simple COUNT(*) Worked #

Your test query SELECT COUNT(*) FROM C_BPARTNER doesn’t use any JOINs, so there’s no way to create duplicate rows. That’s why it matches your tool’s Count Rows—you’re just counting the raw rows in the C_BPARTNER table.

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