时间戳

首页 > 解决方案 > SQL获取产品类别的总卖家

mysql - SQL获取产品类别的总卖家

问题描述

我喜欢这个数据库表：

桌子 categories

id
title
description
status

桌子 products

id
user_id
category_id
description
status

桌子 product_varieties

id
variety_id
product_id
description
wholesale_price
retail_price
status
total

我有查询来获取价格 min，max并且avg在每个类别中：

SELECT `categories`.`title`,
         min(product_varieties.wholesale_price) AS wholesalePriceMin,
         max(product_varieties.wholesale_price) AS wholesalePriceMax,
         avg(product_varieties.wholesale_price) AS wholesalePriceAvg,
         min(product_varieties.retail_price) AS retailPriceMin,
         max(product_varieties.retail_price) AS retailPriceMax,
         avg(product_varieties.retail_price) AS retailPriceAvg
FROM `products`
LEFT JOIN `categories`
    ON `categories`.`id` = `products`.`category_id`
LEFT JOIN `product_varieties`
    ON `product_varieties`.`product_id` = `products`.`id`
WHERE `products`.`id` IN (1,2,3,4)
GROUP BY  `products`.`category_id`

现在我需要通过编辑当前查询来获取每个类别的总用户（卖家）。

我试过这样：

SELECT `categories`.`title`,
         min(product_varieties.wholesale_price) AS wholesalePriceMin,
         max(product_varieties.wholesale_price) AS wholesalePriceMax,
         avg(product_varieties.wholesale_price) AS wholesalePriceAvg,
         min(product_varieties.retail_price) AS retailPriceMin,
         max(product_varieties.retail_price) AS retailPriceMax,
         avg(product_varieties.retail_price) AS retailPriceAvg,
         
    (SELECT user_id,
         COUNT(*)
    FROM products
    GROUP BY  category_id) AS totalUsers
FROM `products`
LEFT JOIN `categories`
    ON `categories`.`id` = `products`.`category_id`
LEFT JOIN `product_varieties`
    ON `product_varieties`.`product_id` = `products`.`id`
WHERE `products`.`id` IN (1,2,3,4)
GROUP BY  `products`.`category_id`

但我的查询不起作用并返回错误消息：

SQLSTATE[21000]: Cardinality violation: 1241 Operand should contain 1 column(s)

如何更正我的查询？

标签： mysqlsql

解决方案

您的子查询必须是相关的，而不是完整的。

SELECT `categories`.`title`,
         min(product_varieties.wholesale_price) AS wholesalePriceMin,
         max(product_varieties.wholesale_price) AS wholesalePriceMax,
         avg(product_varieties.wholesale_price) AS wholesalePriceAvg,
         min(product_varieties.retail_price) AS retailPriceMin,
         max(product_varieties.retail_price) AS retailPriceMax,
         avg(product_varieties.retail_price) AS retailPriceAvg,
         
    (SELECT COUNT(p.user_id)
    FROM products p
    WHERE p.user_id = `products`.user_id) AS totalUsers
FROM `products`
LEFT JOIN `categories`
    ON `categories`.`id` = `products`.`category_id`
LEFT JOIN `product_varieties`
    ON `product_varieties`.`product_id` = `products`.`id`
WHERE `products`.`id` IN (1,2,3,4)
GROUP BY  `products`.`category_id`

PS。也许，COUNT(DISTINCT p.user_id)需要...

推荐阅读