SQL未正确执行联接 [英] SQL not doing the join correctly

问题描述

我有一条带有某些JOIN条件的SQL语句，它对所有人都正常工作，但下面的代码却没有:

I have a SQL statement with some JOIN condition it is working fine for all of them but not the last one the code is below:

SELECT 
    A.EMPL_CTG, 
    B.DESCR AS PrName, 
    SUM(A.CURRENT_COMPRATE) AS SALARY_COST_BUDGET, 
    SUM(A.BUDGET_AMT) AS BUDGET_AMT, 
    SUM(A.BUDGET_AMT)*100/SUM(A.CURRENT_COMPRATE) AS MERIT_GOAL,
    SUM(C.FACTOR_XSALARY) AS X_Programp, 
    SUM(A.FACTOR_XSALARY) AS X_Program,
    COUNT(A.EMPLID) AS EMPL_CNT, 
    COUNT(D.EMPLID),
    SUM(CASE WHEN A.PROMOTION_SECTION = 'Y' THEN 1 ELSE 0 END) AS PRMCNT,
    SUM(CASE WHEN A.EXCEPT_IND = 'Y' THEN 1 ELSE 0 END) AS EXPCNT, 
    (SUM(CASE WHEN A.PROMOTION_SECTION = 'Y' THEN 1 ELSE 0 END)+SUM(CASE WHEN A.EXCEPT_IND = 'Y' THEN 1 ELSE 0 END))*100/(COUNT(A.EMPLID)) AS PEpercent 
FROM 
    EMP_DTL A INNER JOIN EMPL_CTG_L1 B ON A.EMPL_CTG = B.EMPL_CTG  
    INNER JOIN 
    ECM_PRYR_VW C ON A.EMPLID=C.EMPLID 
    INNER JOIN ECM_INELIG  D on D.EMPL_CTG=A.EMPL_CTG and D.YEAR=YEAR(getdate()) 
WHERE 
    A.YEAR=YEAR(getdate()) 
    AND B.EFF_STATUS='A' 
GROUP BY 
    A.EMPL_CTG, 
    B.DESCR 
ORDER BY B.DESCR

COUNT(D.EMPLID)返回与 COUNT(A.EMPLID)相同的值，但是我需要 EMPLID 的计数联接条件中的表D 是否有帮助?

The COUNT(D.EMPLID) is returning the same value as COUNT(A.EMPLID) but I need the count of EMPLIDs for Table D in the join condition, any help?

推荐答案

COUNT() (还有其他

COUNT() (and also the other GROUP BY aggregate functions) doesn't process only the rows from one of the tables.

它们适用于 JOIN 产生的所有行.如果没有的 JOIN 产生42行，则 COUNT(*)和 COUNT(1)返回 42 ，而 COUNT(A.EMPLID)和 COUNT(D.EMPLID)返回非- NULL 值的数量这些列.

They work on all the rows produced by the JOIN. If the JOIN without GROUP BY produces 42 rows then COUNT(*) and COUNT(1) returns 42 while COUNT(A.EMPLID) and COUNT(D.EMPLID) return the number of not-NULL values in those columns.

为了获得从一个表中提取的行数，您应该使用

In order to get the number of rows extracted from one of the tables the you should use COUNT(DISTINCT). It ignores the NULL values and also the duplicates produced by the JOIN.

将 COUNT(D.EMPLID)更改为 COUNT(DISTINCT D.EMPLID).

这篇关于SQL未正确执行联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！