SQL如何为每个状态选择最受欢迎的项目

Question

表模式：

customers
|_state
products
|_product_name
transactions
|_product_id

如何在每个状态名称中返回最受欢迎的项目的名称？

这是最接近的工作示例。

SELECT state, products.product_name, COUNT(products.product_name)
FROM customers INNER JOIN transactions ON customers.customer_id = transactions.customer_id
INNER JOIN products ON transactions.product_id=products.product_id
GROUP BY state, products.product_name;

为每个状态提供所有产品，例如：

 state      name        name count  
 "AK"    "product1"        "1"
 "AK"    "product2"        "4"
 "AK"    "product3"        "1"
 "AR"    "product2"        "1"
 "AR"    "product2"        "2"

再次，我只需要每个州的最高计数。

Answer 1

在给定查询中使用MAX()将给出您的预期结果，这是每个状态的最高计数。

工作查询将是：

SELECT state, MAX(PCount) AS HighestCount
FROM (
    SELECT state, products.product_name, COUNT(products.product_name) AS PCount
    FROM customers 
    INNER JOIN transactions ON customers.customer_id = transactions.customer_id
    INNER JOIN products ON transactions.product_id = products.product_id
    GROUP BY state, products.product_name
) Q
GROUP BY state