如何从 SQL Server 中的 TOP 中排除 LEFT JOINed 表？

假设我有两个书籍表和两个对应版本的表。

我有一个查询如下：

SELECT TOP 10 * FROM
(SELECT hbID, hbTitle, hbPublisherID, hbPublishDate, hbedID, hbedDate
 FROM hardback
 LEFT JOIN hardbackEdition on hbID = hbedID
 UNION 
 SELECT pbID, pbTitle, pbPublisher, pbPublishDate, pbedID, pbedDate
 FROM paperback
 Left JOIN paperbackEdition on pbID = pbedID
) books
WHERE hbPublisherID = 7
ORDER BY hbPublishDate DESC

如果前两本精装书和/或平装书有 5 个版本，则此查询仅返回两本书。但是，我想要TOP 10仅适用于返回的实际图书记录数。有没有一种方法可以让我选择 10 本书，同时仍然获得所有相关的版本记录？

如果相关的话，我没有创建和删除临时表的数据库权限。

谢谢阅读！

Update

澄清一下：平装本表有一个相关的平装本版本表。精装本表有一个相关的精装本版本表。精装本和平装本表格彼此不相关，除非用户（希望如此！）看到它们一起显示。

如果我没理解错的话，你可以通过以下方式获得这 10 本书及其所有相关版本

• Using a WITH返回初始完整结果集的语句
• 使用a选择10本不同的书GROUP BY
• JOIN结果该小组保留了给定 10 本书中的所有信息。

SQL语句

;WITH books AS (
  SELECT  hbID, hbTitle, hbPublisherID, hbPublishDate, hbedID, hbedDate
  FROM    hardback
          LEFT JOIN hardbackEdition on hbID = hbedID
  WHERE   hbPublisherID = 7          
  UNION ALL
  SELECT  pbID, pbTitle, pbPublisher, pbPublishDate, pbedID, pbedDate
  FROM    paperback
          LEFT JOIN paperbackEdition on pbID = pbedID
  WHERE   hbPublisherID = 7
)
SELECT  *
FROM    books b
        INNER JOIN (
          SELECT TOP 10 hbID
          FROM   books
          GROUP BY
                hbID
        ) bt ON bt.hbID = b.hbID

或者如果您希望只编写一次 where 子句

;WITH books AS (
  SELECT  hbID, hbTitle, hbPublisherID, hbPublishDate, hbedID, hbedDate
  FROM    hardback
          LEFT JOIN hardbackEdition on hbID = hbedID
  UNION ALL
  SELECT  pbID, pbTitle, pbPublisher, pbPublishDate, pbedID, pbedDate
  FROM    paperback
          LEFT JOIN paperbackEdition on pbID = pbedID
)
, q AS (
  SELECT  *
  FROM    books
  WHERE   hbPublisherID = 7          
)
SELECT  *
FROM    q b
        INNER JOIN (
          SELECT TOP 10 hbID
          FROM   q
          GROUP BY
                hbID
        ) bt ON bt.hbID = b.hbID