问MySQL:从多个表、子查询中排名？
EN

Stack Overflow用户

提问于 2018-09-20 13:39:54

回答 1查看 756关注 0票数 1

这是一个MySQL问题。我有三个表，列如下：

transactions (table): transact_id, customer_id, transact_amt, product_id,
products (table): product_id, product_cost, product_name, product_category
customers (table): customer_id, joined_at, last_login_at, state, name, email

我想要一个查询，找出每个州和州中最受欢迎的项目。其中一个棘手的部分是一些product_name有多个product_id。因此，我想加入生成两个列的输出的三个表: state和product_name。在此之前，这件事做得很好：

SELECT p.product_name, c.state
FROM products p
INNER JOIN transactions t
ON p.product_id = t.product_id
INNER JOIN customers c
ON c.customer_id = t.customer_id

这将选择所有的产品，以及客户所在的州。问题是，我找不到办法按州对最受欢迎的产品进行排名。我尝试了不同的分组、按顺序和使用子查询，但都没有成功。我怀疑我需要做子查询，但我找不到解决它的方法。预期结果应如下所示：

 most_popular_product | state
 Bamboo               | WA
 Walnut               | MO

任何帮助都将不胜感激。

谢谢!

mysql

join

group-by

multiple-tables

dbeaver

在线客服搭建方案

搭建集AI机器人、在线客服、智能外呼等多功能服务的客服系统，支持快速接入及自搭建客服系统，从而让顾客快速得到解答

回答 1

Stack Overflow用户

回答已采纳

发布于 2018-09-20 13:51:22

您需要一个子查询，该查询获取每个产品在每个状态下的事务数。

SELECT p.product_name, c.state, COUNT(*) AS count
FROM products p
INNER JOIN transactions t
ON p.product_id = t.product_id
INNER JOIN customers c
ON c.customer_id = t.customer_id
GROUP BY p.product_name, c.state

然后编写另一个将其作为子查询的查询，并获取每个状态的最高计数。

SELECT state, MAX(count) AS maxcount
FROM (
    SELECT p.product_name, c.state, COUNT(*) AS count
    FROM products p
    INNER JOIN transactions t
    ON p.product_id = t.product_id
    INNER JOIN customers c
    ON c.customer_id = t.customer_id
    GROUP BY p.product_name, c.state
) AS t
GROUP BY state

最后，将它们结合在一起：

SELECT t1.product_name AS most_popular_product, t1.state
FROM (
    SELECT p.product_name, c.state, COUNT(*) AS count
    FROM products p
    INNER JOIN transactions t
    ON p.product_id = t.product_id
    INNER JOIN customers c
    ON c.customer_id = t.customer_id
    GROUP BY p.product_name, c.state
) AS t1
JOIN (
    SELECT state, MAX(count) AS maxcount
    FROM (
        SELECT p.product_name, c.state, COUNT(*) AS count
        FROM products p
        INNER JOIN transactions t
        ON p.product_id = t.product_id
        INNER JOIN customers c
        ON c.customer_id = t.customer_id
        GROUP BY p.product_name, c.state
    ) AS t
    GROUP BY state
) AS t2 ON t1.state = t2.state AND t1.count = t2.maxcount