限制连接中每组的行数（不限制为 1 行）

鉴于这些表：

TABLE Stores (
 store_id INT,
 store_name VARCHAR,
 etc
);

TABLE Employees (
 employee_id INT,
 store_id INT,
 employee_name VARCHAR,
 currently_employed BOOLEAN,
 etc
);

我想列出每家商店雇佣时间最长的 15 名员工（假设工作时间最低的 15 名员工）employee_id)，或商店的所有员工（如果有 15 名员工）currently_employed='t'。我想用 join 子句来做到这一点。

我发现很多人这样做的例子only对于 1 行，通常是最小值或最大值（单个受雇时间最长的员工），但我基本上想将ORDER BY and a LIMIT连接内部。其中一些示例可以在这里找到：

• 将连接表的结果限制为一行
• MySQL 为每个产品返回 1 个图像

我还找到了逐家进行此操作的不错示例（我没有，我有大约 5000 家商店）：

• 获取每组分组结果的前n条记录

我还看到你可以使用TOP代替ORDER BY and LIMIT，但不适用于 PostgreSQL。

我认为两个表之间的连接子句并不是唯一的（甚至不一定是最好的方法），如果可以通过不同的方式工作的话store_id在员工表内部，所以我愿意接受其他方法。之后随时可以加入。

由于我对 SQL 非常陌生，因此我需要任何理论背景或其他解释来帮助我理解工作原理。

row_number()

获取每组前 n 行的一般解决方案是使用窗口函数row_number():

SELECT *
FROM  (
   SELECT *, row_number() OVER (PARTITION BY store_id ORDER BY employee_id) AS rn
   FROM   employees
   WHERE  currently_employed
   ) e
JOIN   stores s USING (store_id)
WHERE  rn <= 15
ORDER  BY store_id, e.rn;

• PARTITION BY应该使用store_id，保证是唯一的（与store_name).

• 首先识别行employees, then加入stores，这样更便宜。

• To get 15 rows use row_number() not rank()（对于此目的来说，这是错误的工具）。 （尽管employee_id是唯一的，差异不显示。）

LATERAL

自 Postgres 以来的替代方案9.3与匹配的索引相结合通常会表现得更好，尤其从大表中检索小选择时。看：

• LATERAL JOIN 和 PostgreSQL 中的子查询有什么区别？

SELECT s.store_name, e.*
FROM   stores s
CROSS  JOIN LATERAL (
   SELECT *  -- better just the needed columns!
   FROM   employees e
   WHERE  e.store_id = s.store_id
   AND    e.currently_employed
   ORDER  BY e.employee_id
   LIMIT  15
   ) e
-- WHERE ... work with selected stores?
ORDER  BY s.store_name, e.store_id, e.employee_id;

完美的索引将是部分多列索引，如下所示：

CREATE INDEX ON employees (store_id, employee_id) WHERE  currently_employed;

相关示例：

• 使用空列创建唯一约束

这两个版本都排除了没有现有员工的商店。如果需要的话，有很多方法可以解决这个问题LEFT JOIN LATERAL ...