我有两个由person_id相关的表(main_table)和(sub_table)..现在我想从主表中选择person_id,并在sub_table中选择与main_table相关的记录数,其中sub_table中的记录不等于'eco'..现在的问题是当我做这个..查询时只获得不等于'eco'的person_id ...但是我想从查询中选择每个person_id,如果sub_table中的person_id等于'eco'则选择计数0:
SELECT m.person_id, COUNT(*) AS eco FROM (SELECT person_id FROM Main_table
WHERE (person_id ='c')AS m INNER JOIN
(SELECT person_id
FROM sub_table
WHERE person_status != 'eco'
GROUP BY person_id) AS eco ON eco.person_id = m.person_id GROUP BY m.person_id
问题是您要INNER
加入sub_table,因此根据定义,您将结果限制为仅此表中具有不等于条目的人员eco
。
我认为您可以通过只保留联接子表,并在联接条件中使用person_status来做到这一点:
SELECT m.Person_ID,
COUNT(s.Person_ID) AS NonEcoCount
FROM Main_Table m
LEFT JOIN sub_table s
ON s.Person_ID = m.Person_ID
AND s.person_status != 'eco'
GROUP BY m.Person_ID;
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句