按多对多关联记录的计数对行进行排序

发布于 2024-09-26 14:57:36 字数 775 浏览 5 评论 0原文

我知道还有很多其他的 SO 条目看起来像这个，但我还没有找到一个真正回答我的问题的条目，所以希望你们中的一个人可以回答它或向我指出另一个相关的 SO 问题。

基本上，我有以下查询，它返回包含搜索的关键字（本例中为“foobar”）的 CheckIn 的 Venue 。

SELECT DISTINCT v.*
FROM "venues" v
  INNER JOIN "check_ins"   c  ON c."venue_id"     = v."id"
  INNER JOIN "keywordings" ks ON ks."check_in_id" = c."id"
  INNER JOIN "keywords"    k  ON ks."keyword_id"  = k."id"
WHERE (k."name" = 'foobar')

我想要SELECT 和ORDER BY 为每个给定Venue 匹配的Keyword 计数。例如，如果已创建 5 个与该关键字关联的 CheckIn，则应该有一个返回列（称为诸如 keyword_count 之类的列） >) 的值为 5 已排序。

理想情况下，这应该在 SELECT 子句中没有任何查询的情况下完成，或者最好根本没有。

我已经为此苦苦挣扎了一段时间，我的大脑一片空白（也许这一天已经太长了），所以这里将不胜感激。

提前致谢！

原文

I know there are a lot of other SO entries that seem like this one, but I haven't found one that actually answers my question so hopefully one of you can either answer it or point me to another SO question that is related.

Basically, I have the following query that returns Venues that have any CheckIns that contain the searched Keyword ("foobar" in this example).

SELECT DISTINCT v.*
FROM "venues" v
  INNER JOIN "check_ins"   c  ON c."venue_id"     = v."id"
  INNER JOIN "keywordings" ks ON ks."check_in_id" = c."id"
  INNER JOIN "keywords"    k  ON ks."keyword_id"  = k."id"
WHERE (k."name" = 'foobar')

I want to SELECT and ORDER BY the count of the matched Keyword for each given Venue. E.g. if there have been 5 CheckIns that have been created, associated with that Keyword, then there should be a returned column (called something like keyword_count) with the value 5 which is sorted.

Ideally this should be done without any queries in the SELECT clause, or preferably none at all.

I've been struggling with this for a while and my mind is just going blank (perhaps it's been too long a day) so some help would be greatly appreciated here.

Thanks in advance!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

慈悲佛祖 2024-10-03 14:57:36

听起来你需要类似的东西：

SELECT v.x, v.y, count(*) AS keyword_count
FROM "venues" v
  INNER JOIN "check_ins"   c  ON c."venue_id"     = v."id"
  INNER JOIN "keywordings" ks ON ks."check_in_id" = c."id"
  INNER JOIN "keywords"    k  ON ks."keyword_id"  = k."id"
WHERE (k."name" = 'foobar')
GROUP BY v.x, v.y
ORDER BY 3

Sounds like you need something like:

SELECT v.x, v.y, count(*) AS keyword_count
FROM "venues" v
  INNER JOIN "check_ins"   c  ON c."venue_id"     = v."id"
  INNER JOIN "keywordings" ks ON ks."check_in_id" = c."id"
  INNER JOIN "keywords"    k  ON ks."keyword_id"  = k."id"
WHERE (k."name" = 'foobar')
GROUP BY v.x, v.y
ORDER BY 3

回复收藏 0 原文

~没有更多了~