MYSQL为每个“页面id”选择3个结果

Question

我有两张桌子..。第一个是包含(page_id，page_title)行的页面列表。第二个是这些页面上的项目列表，每个项目都有一个价格(item_id、page_id、item_title、item_price)。

我想从每个页面中抓取前三个项目(按最高item_price排序)，具有累积最高价格的页面首先排序。这超出了我的MYSQL能力范围，我正在寻求关于如何让它变得最有效的建议！:)谢谢！

Answer 1

您可以通过几种不同的方法来完成此操作。我要做的就是运行一个查询，说"get me all pages by the sum of the item“，然后在php中循环遍历它们，对于每个页面，执行一个"get me the top 3 item for the current page”。

讲得通?

查询一条(未经测试，写在我的手机上)：

SELECT p.page_name, (SELECT SUM(item_price) FROM items WHERE page_id = p.page_id) AS cumulative_price FROM pages p ORDER BY cumulative_price DESC;

查询二(也未测试)遍历查询一的结果：

SELECT * FROM items WHERE page_id = '$currentPageId' ORDER BY item_price DESC LIMIT 3;

Answer 2

我假设每个page_id都等于item_id，这就是它们联系在一起的方式。(如果不是，请纠正我。)

• 我将把第二个表称为"table2“，而我正在查找的item_id称为"SOME_ITEM_ID”。

SELECT * FROM table2 WHERE item\_id = 'SOME_ITEM_ID‘ORDER BY item\_price DESC;

在英语中，这是在说：

选择item_id为this的table2中的所有内容，并按item_price降序排列列表

这条SQL语句将返回每个命中结果，但您只需在代码中输出前三个。

Answer 3

我的直觉告诉我，可能没有比在应用程序中对所有页面执行for循环更快的方法了，为每个paqe执行一点select item_price from item where page_id = ? order by item_price desc limit 3，并可能将结果粘贴到memcached之类的文件中，这样就不会给数据库带来太大的负担。

但我喜欢挑战，所以无论如何我都会尝试。

SELECT p1.*, i1.*,
 (SELECT count(*)
  FROM items i2
  WHERE i1.item_price < i2.item_price
  AND p1.page_id = i2.page_id) price_rank,
FROM pages p1 
LEFT OUTER JOIN items i1 
 ON p1.page_id = i1.page_id
WHERE price_rank < 3;

这个奇怪的子select可能会为items中的每一行做大量工作。许多其他RDBMses都有一个称为窗口函数的功能，可以更优雅地完成上述任务。例如，如果您使用的是PostgreSQL，则可以这样写：

SELECT p1.*, i1.*, 
    RANK(i1.item_price) 
    OVER (PARTITION BY p1.page_id
          ORDER BY i1.item_price DESC) price_rank 
FROM pages p1 
LEFT OUTER JOIN items i1 
 ON p1.page_id = i1.page_id
WHERE price_rank <= 3;

并且计划器将安排以导致该等级正确出现的顺序来访问行。