时间戳

首页 > 解决方案 > Sql Left or Right Join 一对多分页

sql - Sql Left or Right Join 一对多分页

问题描述

我有一个主表，并通过左外连接或右外连接连接其他表。主表的一行有超过 30 行的连接查询结果。我尝试分页。但问题是我不知道它会为一个主表行结果返回多少行。

例子 ：

主表第一行结果在我的查询中 40 行。主表第二行结果为 120 行。

问题（问题）更新： 对于分页，我需要给 pagesize 选择结果的计数。但我不知道我的选择结果的正确计数。示例我给第 1 页和第 50 页，因此我无法获得正确的结果。我需要为我的主表前 10 结果提供正确的页面大小。也许前 10 行的结果行数为 200，但我的页面大小为 50，这就是问题所在。

我正在使用 Sql 2014。我的 ASP.NET 项目需要它，但并不重要。

样本更新：

这就像搜索酒店进行预订。您的主桌是酒店桌。另一件事是（可媒体的）图像、（可媒体的）视频、（可放置的）位置和可能的（可评论的）评论，它们不止一行，并且与酒店有一对多的关系。对于一家酒店，所有信息的结果将是 100、50 或 10 行。我正在尝试对这个酒店结果进行分页。我需要在我的项目中获得 20 或 30 或 50 家酒店的性能。

示例查询更新：

   SELECT  
      *
   FROM
      KisiselCoach KC
         JOIN WorkPlace WP
            ON KC.KisiselCoachId = WP.WorkPlaceOwnerId
         JOIN Album A
            ON KC.KisiselCoachId = A.AlbumId
            JOIN Media M
               ON A.AlbumId = M.AlbumId
         LEFT JOIN Rating R
            ON KC.KisiselCoachId = R.OylananId 
         JOIN FrUser Fr
            ON KC.CoachId = Fr.UserId 
         JOIN UserJob UJ
            ON KC.KisiselCoachId = UJ.UserJobOwnerId 
            JOIN Job J
               ON UJ.JobId = J.JobId 
         JOIN UserExpertise UserEx
            ON KC.KisiselCoachId = UserEx.UserExpertiseOwnerId
            JOIN Expertise Ex
               ON UserEx.ExpertiseId = Ex.ExpertiseId




Hotel Table : 
HotelId   HotelName
1         Barcelona
2         Berlin


Media Table :
MediaID   MediaUrl      HotelId
1         www.xxx.com   1
2         www.xxx.com   1
3         www.xxx.com   1
4         www.xxx.com   1


Location Table :
LocationId   Adress          HotelId 
1            xyz, Berlin     1
2            xyz, Nice       1
3            xyz, Sevilla    1
4            xyz, Barcelona  1


Comment Table :
CommentId   Comment             HotelId
1           you are cool        1
2           you are great       1
3           you are bad         1
4           hmm you are okey    1

这只是样本！我的数据库中有 9999999 家酒店。想象一家酒店可能有 100 张图片，可能为零。我不能知道这一点。我需要在我的结果中获得 20 家酒店（分页）。但是 20 家酒店可能意味着 1000 行或 100 行。

标签： sqlsql-servertsql

解决方案

首先，您的查询对于表的可读性流/关系编写得不好。我已经更新并缩进以尝试显示表在层次相对论中的相关方式/位置。

你也想分页，让我们回到那个。您是否打算将每条记录显示为可能的项目，或者您是否打算显示“父”级别的数据集...例如，每个媒体、每个用户或其他只有一个实例，然后一旦该条目选择您将显示该实体的详细信息？如果是这样，我会在顶层做一个 DISTINCT 查询，或者至少抓住它必须在下一个级别显示的子记录计数（*）的几列。

此外，混合内连接、左连接和右连接可能会造成混淆。通常，右连接意味着您想要连接右表中的记录。是否可以将其重写为将所有必需的表都放在左侧，而不需要将其左连接到辅助表？

澄清所有这些关系肯定会有助于您尝试摆脱分页的上下文。我会检查评论，但如果冗长，我会编辑您的原始帖子问题，添加更多详细信息而不是长评论。

这是我的 SOMEWHAT 澄清查询重写为我认为关系在您的数据库中的内容。请注意我的缩进显示表 A -> B -> C -> D 的位置以便于阅读。所有这些都是（INNER）JOIN，表示它们都必须在所有相应的表之间匹配。如果某些东西并不总是存在，它们将被更改为 LEFT JOIN

SELECT  
      *
   FROM
      KisiselCoach KC
         JOIN WorkPlace WP
            ON KC.KisiselCoachId = WP.WorkPlaceOwnerId
         JOIN Album A
            ON KC.KisiselCoachId = A.AlbumId
            JOIN Media M
               ON A.AlbumId = M.AlbumId
         LEFT JOIN Rating R
            ON KC.KisiselCoachId = R.OylananId 
         JOIN FrUser Fr
            ON KC.CoachId = Fr.UserId 
         JOIN UserJob UJ
            ON KC.KisiselCoachId = UJ.UserJobOwnerId 
            JOIN Job J
               ON UJ.JobId = J.JobId 
         JOIN UserExpertise UserEx
            ON KC.KisiselCoachId = UserEx.UserExpertiseOwnerId
            JOIN Expertise Ex
               ON UserEx.ExpertiseId = Ex.ExpertiseId

查询的可读性对您自己和/或任何协助或关注您的人都有很大帮助。如果在相应的连接附近没有“on”子句，可能会非常令人困惑。

此外，这是您的 PRIMARY 表，其余的是查找参考表。

每条评论的补充

好的，所以我更新了一个查询，该查询似乎没有示例数据的上下文以及您在帖子中想要的内容。也就是说，我将从仅列出酒店列表和每家酒店的物品数量（*）开始，这样您就可以详细说明您拥有多少东西。就像是

select
      H.HotelID,
      H.HotelName,
      coalesce( MedSum.recs, 0 ) as MediaItems,
      coalesce( LocSum.recs, 0 ) as NumberOfLocations,
      coalesce( ComSum.recs, 0 ) as NumberOfLocations
   from
      Hotel H
         LEFT JOIN
         ( select M.HotelID, 
                  count(*) recs
              from Media M
              group by M.HotelID ) MedSum
            on H.HotelID = MedSum.HotelID
         LEFT JOIN
         ( select L.HotelID, 
                  count(*) recs
              from Location L
              group by L.HotelID ) LocSum
            on H.HotelID = LocSum.HotelID
         LEFT JOIN
         ( select C.HotelID, 
                  count(*) recs
              from Comment C
              group by C.HotelID ) ComSum
            on H.HotelID = ComSum.HotelID
   order by
      H.HotelName
   --- apply any limit per pagination

现在这将返回顶层的每家酒店，以及每个酒店的物品总数，每个可能存在或不存在的个体计数，因此每个子检查都是 LEFT-JOIN。公开 20 家不同酒店的页面。现在，只要有人选择了一家酒店，您就可以深入了解该酒店的位置、媒体和评论。

现在，虽然这可以工作，但必须对每次查询进行这些计数可能会非常耗时。您可能希望将计数器列添加到您的主酒店表中，以表示在此处执行的计数。然后，通过一些每晚的过程，您可以重新更新一次计数，以使它们在所有历史记录中都做好准备，然后仅更新那些自上次输入日期以来有新活动的酒店的计数。不像您一天会有 1,000,000 条新图片、新地点、新评论的帖子，而是 22,000 条，那么这些是您需要重新更新计数的唯一酒店记录。每个增量周期都会很短，仅基于添加的最新条目。对于网络，有一些预聚合计数、总和等在可行的情况下可以节省大量时间。

推荐阅读