使用UPDATE用AVG数据填充第二个表的列

Question

这是我的两个表sold_items和categories。：

sold_items：

​

​

类别：

​

​

sold_items使用categories表中的id_Categories作为外键。

我使用这个命令来尝试填充基于给定的“idCategories”的商品的average_price -因此可以是1或2。我使用这个命令来计算idCategories分类为2的商品的平均价格

UPDATE categories SET average_price = (SELECT AVG(price) FROM sold_items) WHERE idCategories='2'

此命令执行成功，没有错误，但不是计算idCategories = 2的两个商品的平均价格，而是取所有产品的平均价格，并将638.00的值插入到categories表中，这样看起来就像这样：

​

​

正确完成计算的值将是999.50 (即1000+999/2)。我的SQL命令有什么问题？

提前感谢

Answer 1

我建议使用相关子查询：

UPDATE categories c
    SET average_price = (SELECT AVG(si.price)
                         FROM sold_items si
                         WHERE si.iDCategories = c.idCategories
                        )
    WHERE idCategories = 2;

这使得一次更新多个类别--甚至所有类别--变得更加容易。

注意:如果您的If是数字的(您的看起来是数字的)，不要使用单引号进行比较。

Answer 2

您还需要在子查询中指定where idCategories = 2：

UPDATE categories 
SET average_price = (SELECT AVG(price) FROM sold_items WHERE idCategories='2') 
WHERE idCategories=2

Answer 3

试试这个：

CREATE TABLE sold_items (
    idsold_items INT,
    item_number VARCHAR(7),
    title VARCHAR(14),
    URL VARCHAR(3),
    price INT,
    idCategories INT
)

CREATE TABLE categories (
    idCategories INT,
    model VARCHAR(7),
    averageprice FLOAT(10,2),
    keyword VARCHAR(1)
)

INSERT INTO sold_items
VALUES ('1', '14545', 'Equitek CAD', '...', '99', '1'),
       ('2', '454545', 'Equitek', '...', '454', '1'),
       ('3', '456645', 'Samsung', NULL, '999', '2'),
       ('4', '5456464', 'Samsung Galaxy', '', '1000', '2')

INSERT INTO categories
VALUES ('1', 'Equitek', NULL, ''),
       ('2', 'Samsung', NULL, NULL)

UPDATE categories
SET averageprice = CalculatedAverage.averagePrice
FROM categories
JOIN (
    SELECT
        idCategories,
        [averagePrice] = AVG(price)
    FROM sold_items
    GROUP BY idCategories
) CalculatedAverage ON categories.idCategories = CalculatedAverage.idCategories

结果：

idCategories     model    averageprice    keyword
1                Equitek  276.00
2                Samsung  999.00          NULL