主页 / 问题 / 1047099
Accepted
Bakuard
Asked:2020-11-17 14:30:55 +0000 UTC2020-11-17 14:30:55 +0000 UTC

在 UPDATE 中使用 IFNULL 函数

  • 772

再会。

任务：有一张桌子DISHES：

CREATE TABLE DISHES (
   id INT AUTO_INCREMENT PRIMARY KEY, 
   dish_name VARCHAR_IGNORECASE(256) NOT NULL, 
   picture_name VARCHAR(256), 
   unit VARCHAR(256), 
   price DECIMAL(64,32) DEFAULT 0 CHECK(price >= 0), 
   calories DECIMAL(64,32) DEFAULT 0 CHECK(calories >= 0), 
   UNIQUE(dish_name)
);

和观点DISH_PRICE_CALORIES：

CREATE VIEW DISH_PRICE_CALORIES 
    (dish_id, price, calories) AS 
    SELECT 
    DISH_TO_PRODUCTS_COMPOSITION.dish_id, 
    SUM(PRODUCTS.price * DISH_TO_PRODUCTS_COMPOSITION.amount_product),
    SUM(PRODUCTS.calories * DISH_TO_PRODUCTS_COMPOSITION.amount_product)
    FROM 
    DISH_TO_PRODUCTS_COMPOSITION, PRODUCTS 
    WHERE DISH_TO_PRODUCTS_COMPOSITION.product_id = PRODUCTS.id 
    GROUP BY DISH_TO_PRODUCTS_COMPOSITION.dish_id;

您需要使用视图中的数据来更新列price和calories表DISHES中的菜品。id应该考虑到当视图DISH_PRICE_CALORIES不包含具有给定id.

我如何尝试解决问题：我尝试了以下查询：

UPDATE DISHES 
    SET(price, calories) = 
       (SELECT IFNULL(DISH_PRICE_CALORIES.price,0), 
               IFNULL(DISH_PRICE_CALORIES.calories,0)  
        FROM DISH_PRICE_CALORIES 
        WHERE DISH_PRICE_CALORIES.dish_id = DISHES.id) 
    WHERE DISHES.id = 115;

UPDATE DISHES 
   SET(price, calories) = 
       CASEWHEN( 
          EXISTS(
             SELECT DISH_PRICE_CALORIES.price, DISH_PRICE_CALORIES.calories 
             FROM DISH_PRICE_CALORIES 
             WHERE DISH_PRICE_CALORIES.dish_id = DISHES.id 
          ), 
          (SELECT DISH_PRICE_CALORIES.price, DISH_PRICE_CALORIES.calories 
           FROM DISH_PRICE_CALORIES 
           WHERE DISH_PRICE_CALORIES.dish_id = DISHES.id), 
           0 
       )
    WHERE DISHES.id = 115;

UPDATE DISHES 
   SET(price, calories) = 
       IFNULL((SELECT DISH_PRICE_CALORIES.price,
                      DISH_PRICE_CALORIES.calories  
               FROM DISH_PRICE_CALORIES 
               WHERE DISH_PRICE_CALORIES.dish_id = DISHES.id),0) 
    WHERE DISHES.id = 115;

（115我在这里给出的数字只是一个例子。数字可以是任何东西。）

它们中的每一个都被执行，但列price和calories表DISHES仍然包含值NULL。

问题：如果视图中没有具有给定 ID 的菜的行，我如何0在列price和calories表中输入值？DISHESDISH_PRICE_CALORIES

sql

2 个回答

  1. 不是 H2 专家，但在我看来，问题在于在 IFNULL 中你试图传递一个字符串，而不是一个表达式。

    看起来应该是这样的：

    UPDATE DISHES 
   SET(price, calories) = 
      (IFNULL((SELECT DISH_PRICE_CALORIES.price,
               FROM DISH_PRICE_CALORIES 
               WHERE DISH_PRICE_CALORIES.dish_id = DISHES.id),0),
       IFNULL((SELECT DISH_PRICE_CALORIES.calories,
               FROM DISH_PRICE_CALORIES 
               WHERE DISH_PRICE_CALORIES.dish_id = DISHES.id),0))
   WHERE DISHES.id = 115;
    • 1
  2. Best Answer

    上面，德国鲍里索夫给出了一个几乎正确的答案。问题实际上是我试图将字符串传递给 IFNULL，而不是表达式。但他提交的查询包含几个语法错误。修复它们后，我得到了：

    UPDATE DISHES 
   SET price = 
       IFNULL(SELECT DISH_PRICE_CALORIES.price 
              FROM DISH_PRICE_CALORIES 
              WHERE DISH_PRICE_CALORIES.dish_id = DISHES.id,0), 
       calories = 
       IFNULL(SELECT DISH_PRICE_CALORIES.calories 
              FROM DISH_PRICE_CALORIES 
              WHERE DISH_PRICE_CALORIES.dish_id = DISHES.id,0) 
WHERE DISHES.id = 115;

    （我再说一遍，我在这里给出的数字 115 只是一个例子。这个数字可以是任何东西。）

    • 1

相关问题