查询返回"as a column"内部查询

Question

我喜欢在表格中列出所有公司以及每个特定公司的管理员数量。

表格：

companies
id
name
...
users
id
company_id
...
groups ('id' = 1 has 'name' = admin)
id
name 
users_groups
id
user_id
group_id

为了列出所有"公司"，我写了这篇文章：

SELECT companies.name
FROM companies

为了获得一个特定"公司"（具有给定 ID）中的"管理员"数量，我写了这个

SELECT COUNT (users.id) 
FROM users, companies, users_groups WHERE
users.company_id = companies.id AND 
users_groups.user_id = users.id AND
users_groups.group_id = 1

那么我该如何合并这两个问题呢？此操作失败：

SELECT 
  companies.name, 
    (
      SELECT COUNT (users.id) 
      FROM users, companies, users_groups WHERE
      users.company_id = companies.id AND 
      users_groups.user_id = users.id AND
      users_groups.group_id = 1
    ) 
  as admins_in_company
FROM users, companies, users_groups

Answer 1

使用显式连接语法和计数（不同...）：

select c.name, count(distinct u.id)
from companies c
inner join users u
  on u.company_id = c.id
inner join users_groups ug
  on ug.user_id = u.id
where ug.group_id = 1
group by c.name

对于所有公司：

select c.name, count(distinct u.id)
from companies c
left join users u
  on u.company_id = c.id
left join users_groups ug
  on ug.user_id = u.id
  and ug.group_id = 1
group by c.name

Answer 2

SELECT c.name, COUNT(DISTINCT u.id) AS num_admins
    FROM groups AS g
    JOIN users_groups AS ug   ON ug.group_id = g.id
    JOIN users AS u           ON u.id = ug.user_id
    JOIN companies AS c       ON c.id = u.company_id
    WHERE g.group_id = 1
      AND g.name = 'admin'
    GROUP BY u.company_id;

目前尚不清楚您是否需要COUNT(DISTINCT u.id)或只是COUNT(*)。

我按查看顺序列出了 4 个表。 （这不是必需的，但使用户更容易阅读和思考它。 首先是groups，它具有所有"过滤( ). Then it moves through the other tables all the way to getting the company.name . Then the按and its计数分组（不同...' 应用。

多

：多模式 （users_groups） 设计提示：http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table

groups -- group_id and group.name are in a 1:1 relationship, yes? If you know that it is group_id = 1 , you can get rid of the table completely from the query. If not, then be sure to have该表中的 INDEX（name）' 组。