计数次数ID出现在表格中并返回行 [英] Count times ID appears in a table and return in row

问题描述

SELECT
Boats.id, Boats.date, Boats.section, Boats.raft, 
river_company.company, river_section.section AS river 
FROM Boats 
INNER JOIN river_company ON Boats.raft = river_company.id 
INNER JOIN river_section ON Boats.section = river_section.id 
ORDER BY Boats.date DESC, river, river_company.company

返回我需要的一切.但是，我将如何添加[Photos]表并计算Boats.id在其中出现的次数，并将其添加到返回的行中.

Returns everything I need. But how would I add a [Photos] table and count how many times Boats.id occurs in it and add that to the returned rows.

所以如果#17号船有5张照片，我希望#17号船的记录说PhotoCount = 5

So if there are 5 photos for boat #17 I want the record for boat #17 to say PhotoCount = 5

推荐答案

您可以将LEFT JOIN添加到子查询中，如下所示:

You could add a LEFT JOIN to a sub query as follows:

LEFT JOIN (SELECT COUNT(p.id) num, p.boatID FROM photos p GROUP BY p.boatID) 
          sub_photos ON (sub_photos.boatID = Boats.id)

然后在SELECT字段中引用sub_photos.num.

它看起来像这样:

SELECT     Boats.id, Boats.date, Boats.section, Boats.raft, 
           river_company.company, river_section.section AS river,
           sub_photos.num AS number_of_photos
FROM       Boats 
INNER JOIN river_company ON Boats.raft = river_company.id 
INNER JOIN river_section ON Boats.section = river_section.id 
LEFT JOIN  (SELECT COUNT(p.id) num, p.boatID FROM photos p GROUP BY p.boatID) 
           sub_photos ON (sub_photos.boatID = Boats.id)
ORDER BY   Boats.date DESC, river, river_company.company

这篇关于计数次数ID出现在表格中并返回行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！