SELECT u.name, count( c.id ) AS numbers
FROM oa_system_username AS u
LEFT JOIN oa_gues_contact_record AS c ON c.admin = u.system_id
WHERE u.state =1
GROUP BY u.system_id
ORDER BY numbers ASC 这样查询出来的结果为人员、数量张三 0
李四 0
王五 3
赵六 4现在的需要在 oa_gues_contact_record 表里加个条件,为 c.type = 1 语句如下SELECT u.name, count( c.id ) AS numbers
FROM oa_system_username AS u
LEFT JOIN oa_gues_contact_record AS c ON c.admin = u.system_id
WHERE u.state =1 and c.type = 1
GROUP BY u.system_id
ORDER BY numbers ASC 这个语句执行后显示的结果就变成了人员、数量王五 3
赵六 4张三李四不显示了,我是想让他们显示的,显示出来数量为0请问这个条件如何加,能让他们显示出来?
FROM oa_system_username AS u
LEFT JOIN oa_gues_contact_record AS c ON c.admin = u.system_id
WHERE u.state =1
GROUP BY u.system_id
ORDER BY numbers ASC
或者SUM(CASE c.type WHEN1THEN 1 ELSE 0 END)
之类的
FROM oa_system_username AS u
LEFT JOIN oa_gues_contact_record AS c ON c.admin = u.system_id
WHERE u.state =1
GROUP BY u.system_id
ORDER BY numbers ASC 楼主是不是还要加什么条件啊?!建议一次性把问题描述完整!问题说明越详细,回答也会越准确!参见如何提问。(提问的智慧)