问题描述
我有一条带有某些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 EMPLID
s for Table D in the join condition, any help?
推荐答案
COUNT()
(还有其他 GROUP BY
聚合函数)不只处理其中一个表中的行.
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.
为了获得从一个表中提取的行数,您应该使用 COUNT(DISTINCT)
.它会忽略 NULL
值以及 JOIN
产生的重复项.
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未正确执行联接的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!