有2个表,地点表和简历个人信息表
我想查询地点表里出现的所有地点及每个地点对应的简历数,但是如果简历个人信息表里没出现某一地点,则查询结果则不显示,我想让结果显示0,例如:北京 0。
请问应该怎么写?
我的原SQL语句:SELECT
COUNT(*) RESUMEQUANTITY,
W.WORKPLACENAME
FROM
RE_PERSONAL P,
S_REWORKPLACE W
WHERE
P.WORKPLACE = W.WORKPLACEID
GROUP BY W.WORKPLACENAME
我想查询地点表里出现的所有地点及每个地点对应的简历数,但是如果简历个人信息表里没出现某一地点,则查询结果则不显示,我想让结果显示0,例如:北京 0。
请问应该怎么写?
我的原SQL语句:SELECT
COUNT(*) RESUMEQUANTITY,
W.WORKPLACENAME
FROM
RE_PERSONAL P,
S_REWORKPLACE W
WHERE
P.WORKPLACE = W.WORKPLACEID
GROUP BY W.WORKPLACENAME
COUNT(*) RESUMEQUANTITY,
W.WORKPLACENAME
FROM
RE_PERSONAL P,
S_REWORKPLACE W
WHERE
W.WORKPLACEID=P.WORKPLACE(+)
GROUP BY W.WORKPLACENAME
刚才忘了说,我用的数据库是 SQL Server
COUNT(RESUMEQUANTITY) ,
W.WORKPLACENAME
FROM
RE_PERSONAL P,
S_REWORKPLACE W
WHERE
P.WORKPLACE = W.WORKPLACEID
GROUP BY W.WORKPLACENAME
不知道你事不事这个意思
ISNULL(COUNT(*),0) RESUMEQUANTITY,
W.WORKPLACENAME
FROM
RE_PERSONAL P
left join
S_REWORKPLACE W
on P.WORKPLACE = W.WORKPLACEID
GROUP BY W.WORKPLACENAME
isnull(count(*),0) resumnquantity,
w.workplacename
from
S_REWORKPLACE W
right join
RE_PERSONAL P
on P.WORKPLACE = W.WORKPLACEID
GROUP BY W.WORKPLACENAME
ISNULL(COUNT(*),0) RESUMEQUANTITY,
W.WORKPLACENAME
FROM
S_REWORKPLACE W
left join
RE_PERSONAL P
on P.WORKPLACE = W.WORKPLACEID
GROUP BY W.WORKPLACENAME
是不是这个原因:
地点表(S_REWORKPLACE)里存在这个地点(北京),简历个人信息表(RE_PERSONAL)里不存在这个地点(北京)的简历,虽然不满足P.WORKPLACE = W.WORKPLACEID这个条件,但由于是左联接,只要S_REWORKPLACE表里存在这个地点,那么查询结果就显示为1。我这么猜测的,谁指点一下。