如何为另一个表中的每个连接行返回特定数量的行(大于1)

5sxhfpxr 于 2021-07-26 发布在 Java

关注(0)|答案(2)|浏览(251)

下面是一个SQLFIDLE，展示了我的用例的示例数据：http://sqlfiddle.com/#!9/ae57c5d/6型
我有一张table category ( id , title )包含类别列表和表 item ( id , name , category_id )使用外键 category_id 它指向一个类别，不能是 NULL .
如果我现在想选择最新的 item 从每个 category ，我可以通过运行以下查询：

SELECT item.id, item.name, category.title
FROM category
JOIN (
  SELECT MAX(id) AS max_id, category_id 
  FROM item 
  GROUP BY category_id
) AS i_max ON (i_max.category_id = category.id)
JOIN item ON (item.id = i_max.max_id)
ORDER BY item.id DESC

这让我明白：

+----+------------+-------+
| id | name       | title |
+----+------------+-------+
| 15 | Sydney     | City  |
| 10 | Tesla      | Car   |
| 5  | Pear       | Fruit |
+----+------------+-------+

但是如果我想从每个类别中得到3个最新的条目，我该如何编写查询呢？
在这种情况下，我的预期输出是这样的（输出中类别的顺序是不相关的；当关注输出中的任何给定类别时，项目的顺序应为降序）：

+----+------------+-------+
| id | name       | title |
+----+------------+-------+
| 15 | Sydney     | City  |
| 14 | London     | City  |
| 13 | Helsinki   | City  |
| 10 | Tesla      | Car   |
| 9  | Ferrari    | Car   |
| 8  | Mitsubishi | Car   |
| 5  | Pear       | Fruit |
| 4  | Watermelon | Fruit |
| 3  | Apple      | Fruit |
+----+------------+-------+

sql mysql Join

来源：https://stackoverflow.com/questions/62228917/how-do-i-return-a-specific-number-of-rows-larger-than-1-for-each-of-the-joine

2条答案

按热度按时间

gijlo24d1#

假设您使用的是mysql 8+，那么 ROW_NUMBER 在这里：

WITH cte AS (
    SELECT i.id, i.name, c.title,
        ROW_NUMBER() OVER (PARTITION BY c.title ORDER BY i.id DESC) rn
    FROM category c
    INNER JOIN item i ON i.category_id = c.id
)

SELECT id, name, title
FROM cte
WHERE rn <= 3
ORDER BY title, id DESC;

赞(0）回复(0）举报 2021-07-26

polkgigr2#

如前所述，如果将db升级到8+，则可以使用分析函数，如 DENSE_RANK() ```
SELECT id, name, title
FROM
(
SELECT i.id, i.name, c.title,
DENSE_RANK() OVER (PARTITION BY i.category_id ORDER BY i.id DESC ) AS dr
FROM category c
JOIN item i
ON i.category_id = c.id
) t
WHERE dr <= 3
ORDER BY t.id DESC

演示
具有关系的结果（具有相同排名的值）也包含在使用的案例的结果集中 `DENSE_RANK()` 函数，而用于 `ROW_NUMBER()` 没有。

赞(0）回复(0）举报 2021-07-26

我来回答

如何为另一个表中的每个连接行返回特定数量的行(大于1)

2条答案

相关问题

热门标签

最新问答