计算ID在表中出现的次数并按行返回

Question

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 在其中出现的次数并将其添加到返回的行中。

因此，如果 17 号船有 5 张照片，我希望 17 号船的记录为 PhotoCount = 5

Answer 1

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

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

Answer 2

我认为boatsId 是照片表中的一列？最简单的方法是使用子选择：

SELECT ...whatever columns...,

(SELECT COUNT(*) FROM Photos WHERE boatsId = Boats.id)

FROM ...rest of your query

Answer 3

SELECT Boats.id,
       Boats.date,
       Boats.section,
       Boats.raft,
       river_company.company,
       river_section.section AS river
       (SELECT count(*) FROM Photos WHERE boatsID = Boats.id)
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

尝试一下这个。

Answer 4

另一种方式：

SELECT 
Boats.id, Boats.date, Boats.section, Boats.raft,  
river_company.company, river_section.section AS river  
Count(Photos.ID) AS PhotoCount
FROM Boats  
INNER JOIN river_company ON Boats.raft = river_company.id  
INNER JOIN river_section ON Boats.section = river_section.id  
OUTER JOIN Photos ON Boats.id = Photos.BoatID ORDER BY Boats.date DESC, river, river_company.company 
GROUP BY
    Boats.id, Boats.date, Boats.section, Boats.raft, river_company.company, river_section.section

Answer 5

您尚未提供有关照片表结构的任何信息，但类似的内容应该适合您。

SELECT
Boats.id, Boats.date, Boats.section, Boats.raft, 
river_company.company, river_section.section AS river,
PhotoCount.NumberOfBoats as PhotoCount 
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 b2.Id, Count(b2.Id) as NumberOfBoats
    From Boats b2
    Join Photos p on b2.Id = p.BoatId
    Group By b2.Id
)BoatCount on BoatCount.Id = Boats.Id

ORDER BY Boats.date DESC, river, river_company.company

Answer 6

另一种方式

SELECT DISTINCT
Boats.id, Boats.date, Boats.section, Boats.raft,  
river_company.company, river_section.section AS river,
count(photos.boatid) OVER(PARTITION BY photos.boatid)
FROM Boats  
INNER JOIN river_company ON Boats.raft = river_company.id  
INNER JOIN river_section ON Boats.section = river_section.id
LEFT JOIN photos on photos.boatid = Boats.id
ORDER BY Boats.date DESC, river, river_company.company 

Answer 7

Select YourID, COUNT(*) FROM YourTable GROUP BY YourID

当我尝试根据 ID 或任何其他列查找记录计数可能不同的位置时，此方法效果很好。