SQL 一对多 JOIN 无需子查询？

Question

我有一个与 1 到 n 查询相关的问题，我希望有人能够解答这个问题。

这就是我想要实现的目标。

我有几个房子，每个房子可能有多个住户。我想选择 5 套房屋以及所有相关的居住者。我知道有两种方法。其中最常见的是一个 select 语句来获取 5 个房屋，然后使用 IN 条件的第二个 select 语句来获取关联的居住者。另一种方法是使用一个查询和一个子查询来实现两个查询的变体。

有没有其他方法可以在不使用子查询的情况下完成？

更新：

似乎一致认为使用子查询是不可能实现这一点的。

Answer 1

您通常会使用 JOIN 从两个表中获取相关数据。

SELECT
   *
FROM
   (SELECT TOP 5 * FROM Homes ORDER BY something) H
   JOIN
   Occupants O ON H.HomeID = O.HomeID

但是，TOP 5 只需要应用于 Homes。 MySQL 会是 LIMIT，而不是 TOP。

编辑：

窗口/排名函数仍然需要子查询或某些间接（例如 CTE）以允许对生成的数字进行过滤。

SELECT
   *
FROM
   (SELECT *, ROW_NUMBER() OVER (ORDER BY something) AS RowNum
    FROM Homes) H
   JOIN
   Occupants O ON H.HomeID = O.HomeID
WHERE
   RowNum <= 5

;WITH CTE AS
(
   SELECT
    *, ROW_NUMBER() OVER (ORDER BY something) AS RowNum
   FROM
    Homes
 )
SELECT
   *
FROM
   CTE H
   JOIN
   Occupants O ON H.HomeID = O.HomeID
WHERE
   RowNum <= 5

Answer 2

您可以执行类似的操作，具体取决于数据库引擎支持：

select *
from
   (
       select top 5
           home_id,
           ... -- other columns
       from 
           home
       order by
           awesomeness desc
   ) h
       inner join occupant o on o.home_id = h.home_id

Answer 3

使用内连接。每个房屋都会被列出多次，该房屋中的每个居住者都会被列出一次。

select Home.HomeName, Occupant.OccupantName from Home 
inner join Occupant on Home.Id=Occupant.HomeId

然后你会得到：

Home1 OccupantA
Home1 OccupantB
Home2 OccupantC
Home2 OccupantD

Answer 4

如果您使用的是 SQL Server（2005 及更高版本），您可以使用 CTE

WITH MyTopFive (MyId, MyOtherField, SomeField) AS
(
SELECT TOP 5 MyId, MyOtherField, SomeField FROM Homes ORDER BY SomeField
)

SELECT * 
FROM Mytopfive t5
JOIN Occupants o ON o.myid = t5.myid

gbn 拥有最好的通用数据库解决方案。

Answer 5

特定于 SQL Server

是的，您可以使用联接来执行此操作。您没有显示您的数据库模式，但如果我猜测您可能想要这样的东西：

 select *, RANK() OVER (PARTITION BY home.homeid ORDER BY home.homeid DESC) as houserank
 from home
 join occupant on home.homeid = occupant.homeid
 where  houserank < 6