mysql - 如何将连接表的条件计数放在同一个连接表的单独列中

Question

比方说，我有两个表 - people 和 bonus

------------
people
------------
people_id | company_id  | job_id
  1       |     1       |    2
  2       |     1       |    4
  3       |     2       |    1
  4       |     2       |    3
  5       |     3       |    5

------------
bonus
------------
job_id    | bonus_id 
  1       |     101    
  2       |     102    
  3       |     103

现在，我想要一个如下所示的连接表

-------------
JOINED TABLE 
-------------
people_id | company_id  | job_id | bonus_id | no_of_bonus_for_company
  1       |     1       |    2   |   102    |      1
  2       |     1       |    4   |   NULL   |      1
  3       |     2       |    1   |   101    |      2
  4       |     2       |    3   |   103    |      2
  5       |     3       |    5   |   NULL   |      0

我需要在 people_id 中包含主要搜索词，如 -

SELECT p.people_id,
       p.company_id,
       p.job_id,
       b.bonus_id
FROM people p
LEFT JOIN bonus b
     ON p.job_id = b.job_id
WHERE p.people_id IN (1,2,3,4,5)
ORDER BY p.people_id ASC;

但是如何获取连接表的第五列呢？它实际上算没有。连接表本身中每个公司 ID 的奖金 ID。

Answer 1

我只能假设您只告诉我们部分情况，所以我将保留对数据库架构、规范化等的判断。

鉴于提供的事实，您可以通过以下方式检索信息。

注意:这是 TSQL 语法，但我不认为 mySQL 语法应该有很大不同，即 ISNULL -> IFNULL

   SELECT p.people_id
        , p.company_id
        , p.job_id
        , b.bonus_id
        , ISNULL((
                   SELECT COUNT(pt.Job_Id)
                     FROM Bonus bt 
               INNER JOIN People pt
                       ON pt.job_Id = bt.job_Id
                    WHERE pt.company_Id = p.company_Id
                 GROUP BY pt.company_Id                 
                 ), 0) AS no_of_bonus_for_company
     FROM People p 
LEFT JOIN Bonus b
       ON p.job_Id = b.job_Id