sql-server - 如何在SQL Server的TOP中排除LEFT JOINed表？

Question

假设我有两本书和两张相应版本的书。

我有一个查询，如下所示：

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本实际的书籍，并仍然获得其所有相关的版本记录？

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

谢谢阅读！

更新资料

需要说明的是：平装表具有相关的平装版本表。精装表具有关联的精装版本表。精装本和平装本表彼此无关，只是与（希望！）看到它们一起显示的用户无关。

Answer 1

没那么容易。您只需要将Top 10应用于精装本和平装本表，而无需联接。然后将结果加入数据。

仅当hbID和pbID始终唯一时，以下查询才有效。如果没有，它将变得更加复杂。您需要将它们分开或在查询中添加另一列以区分它们。

SELECT *
FROM
  (SELECT hbID as id, hbTitle, hbPublisherID, hbPublishDate, hbedID, hbedDate
   FROM hardback
   LEFT JOIN hardbackEdition on hbID = hbedID
   UNION 
   SELECT pbID as id, pbTitle, pbPublisher, pbPublishDate, pbedID, pbedDate
   FROM paperback
   Left JOIN paperbackEdition on pbID = pbedID
  ) books
INNER JOIN 
  (SELECT TOP 10 * 
  FROM
    (SELECT hbID as id, hbPublisherID as publishedId, hbPublishDate as publishDate
     FROM hardback
     UNION 
     SELECT pbID as id, pbPublisherID as publishedId, pbPublishDate as publishDate
     FROM paperback
    ) 
  WHERE publisherID = 7
  ORDER BY publishDate DESC
  ) topTen 
  on books.id = TopTen.id