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

问题描述：

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

因此，如果有5张为舟＃17我想为船＃17的记录说PHOTOCOUNT = 5

答

你可以一个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

丹尼尔，除了小名改正之外，它的官员！谢谢！ Boats.boatID = Boats.ID – Landmine 2010-04-29 07:20:06

@Tyler：刚刚注意到了，修复了......我很高兴这有助于:) – 2010-04-29 07:22:41

我也是！你救了我的夜晚！ – Landmine 2010-04-29 07:25:23

答

我相信boatsId是在照片表中的列？最简单的方法是用一个子查询：

SELECT ...whatever columns..., 

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

FROM ...rest of your query

你会是对的。 – Landmine 2010-04-29 07:15:42

子查询返回的值超过1。 – Landmine 2010-04-29 07:17:24

如果你使用这种方法，你应该过滤船ID的子选择。我认为加入子查询对于这种事情更好。 – codingbadger 2010-04-29 07:20:34

答

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

给这一个尝试。

微小的变化，它很好用！谢谢。 SELECT Boats.id， Boats.date， Boats.section， Boats.raft， river_company.company， river_section.section过江，（SELECT COUNT（*）FROM照片WHERE boatID = Boats.id）作为PicCount 从船上 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_company.company – Landmine 2010-04-29 07:28:06

答

另一种方式：

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

答

您还没有提供关于照片表的结构，但这样的事情应该为你工作的任何信息。

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

去与第一篇文章，但还是谢谢你为你的时间。 – Landmine 2010-04-29 07:26:32

没问题，欢呼声 – codingbadger 2010-04-29 07:28:21

我会尽量在将来发布更多细节。再次感谢。 – Landmine 2010-04-29 07:29:30

答

另一种方式

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

答

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

这一切正常，当我试图追查其中记录计数可能基于ID或任何其他列是不同的。

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

相关推荐