我怎样才能排除LEFT加入从SQL Server TOP表？(How can I exclude L

比方说，我有书两个表和它们对应版本的两个表。

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

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:

没那么容易。 你需要前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

Answer 2:

如果我理解正确的话，你可以与所有相关版本由获得10本书

• 使用WITH语句返回初始，完整的结果集
• 通过使用选择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

Answer 3:

这应该抓住与出版商7精装十个最近发表标题：

select  *
from    (
        select  top 10 title
        from    hardback
        where   hbPublisherID = 7
        group by
                title
        order by
                hbPublishDate desc
        ) top_titles
left join
      hardback
on    hardback.hbTitle = top_titles.title
left join
      paperback
on    paperback.pbTitle = top_titles.title

文章来源: How can I exclude LEFT JOINed tables from TOP in SQL Server?