SQL - 复杂的查找查询

发布于 2024-11-27 18:59:23 字数 417 浏览 0 评论 0原文

我需要有关复杂查询的帮助。我有一个 Venues 表、一个 Tag 表和一个 Venues_Tag_lookup 表。当我在屏幕上显示单个场地的详细信息时，我希望能够显示与当前场地类似的其他场地。

这需要一个返回匹配标签数量最多的前 5 个场所的查询。（我正在使用 MSSQL）

这是我的表外观的简单布局。

Venues_tbl
----------
VenueId
Venue_name

Tag_tbl
---------
TagId
Tag_name

Venues_Tag_lookup
------------------
Venue_tagId
VenueId
TagId

如果您有任何疑问，请询问。

提前致谢。

原文

I need help with a complicated query. I have a Venues table, a Tag table and a Venues_Tag_lookup table. When I have the detail of a single venue on screen I would like to be able to show other venues that are similar to the current venue.

This will require a query that returns the top 5 venues with the most amount of matching tags. (I'm using MSSQL)

Here is a simple layout of how my tables look.

Venues_tbl
----------
VenueId
Venue_name

Tag_tbl
---------
TagId
Tag_name

Venues_Tag_lookup
------------------
Venue_tagId
VenueId
TagId

If you have any question please ask.

Thanks in advance.

分享到QQ

分享到微博

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

发布评论

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

寄居人 2024-12-04 18:59:23

SELECT TOP 5
   V.Venue_name
FROM
   -- this = tags for this venue
   Venues_Tag_lookup this
   JOIN
   -- others = tags for other venues
   Venues_Tag_lookup others
            --see what matches, there will be a big pile of them
            ON this.TagId = others.TagId
   JOIN
   Venues_tbl V ON others.VenueID = V.VenueID
WHERE
   --filter to this and others
   this.VenueID = @TheOneOnScreen
   AND
   others.VenueID <> @TheOneOnScreen
GROUP BY
   --collapse to other venues ...
   V.Venue_name
ORDER BY
   -- ... and simply COUNT matches
   COUNT(*) DESC

SELECT TOP 5
   V.Venue_name
FROM
   -- this = tags for this venue
   Venues_Tag_lookup this
   JOIN
   -- others = tags for other venues
   Venues_Tag_lookup others
            --see what matches, there will be a big pile of them
            ON this.TagId = others.TagId
   JOIN
   Venues_tbl V ON others.VenueID = V.VenueID
WHERE
   --filter to this and others
   this.VenueID = @TheOneOnScreen
   AND
   others.VenueID <> @TheOneOnScreen
GROUP BY
   --collapse to other venues ...
   V.Venue_name
ORDER BY
   -- ... and simply COUNT matches
   COUNT(*) DESC

回复收藏 0 原文

迟月 2024-12-04 18:59:23

以下查询可能有帮助：

-- params = @venueId, @tagId

select
        venueId,
        venueName,
        (select count * from venues_tag_lookup vtl where vtl.venueid=v.venueid )tagCount
from venues_tbl v
where venueId in(
  select venueId from Venues_tag_lookup where tagId = @tagId
)
and venueId  @venueId
order by 3 desc

following query may help:

-- params = @venueId, @tagId

select
        venueId,
        venueName,
        (select count * from venues_tag_lookup vtl where vtl.venueid=v.venueid )tagCount
from venues_tbl v
where venueId in(
  select venueId from Venues_tag_lookup where tagId = @tagId
)
and venueId  @venueId
order by 3 desc

回复收藏 0 原文

~没有更多了~