mysql where OR having

Question

我有一个用户表，其中一些用户有关联的文章，还有一些用户的type = writer。我想显示所有拥有文章或type = writer的用户。因此，应该显示所有作者，而其他用户类型只有在他们有文章时才会显示。

到目前为止，这就是我的查询，它省略了没有文章的作者。

SELECT u.name, u.type, COUNT(a.id) count
FROM users u
LEFT JOIN articles a on u.id = a.writer_id
GROUP BY u.name
HAVING count > 0

添加以下WHERE子句显然会排除具有项目的其他用户类型。

WHERE u.type = 'writer'

我需要对这两个结果集进行联合吗？

Answer 1

我想你是在找这样的东西

SELECT 
  u.name, 
  u.type, 
  COUNT(a.id) count
FROM users u
LEFT JOIN articles a ON u.id = a.writer_id
WHERE 
  u.type='writer' --all users that are writers
  OR 
  a.writer_id IS NOT NULL  --all users that have at least one article
GROUP BY 
  u.name
--removed the having clause as it seems it may be possible that a writer has no articles.

Answer 2

只需更改WHERE子句，以允许具有匹配文章记录的任何用户：

SELECT u.name, u.type, COUNT(a.id) count
FROM users u
LEFT JOIN articles a on u.id = a.writer_id
WHERE u.type = 'writer' OR a.writer_id IS NOT NULL
GROUP BY u.name
HAVING count > 0