如何从select mysql中省略列

kmpatx3s 于 2021-06-25 发布在 Mysql

关注(0)|答案(1)|浏览(319)

我有以下疑问：

INSERT INTO kitchen (account_id, ingredient)
    SELECT
        kitchen.account_id,
        ingredients.ingredient,
        kitchen.ingredient,
        count(*) c
    FROM planned_meals
        JOIN meal
        JOIN kitchen
        JOIN ingredients
    WHERE planned_meals.mid = meal.mid
        AND ingredients.rid = meal.recipe_id
        AND ingredients.ingredient != ""
        AND ingredients.ingredient NOT LIKE CONCAT("%",kitchen.ingredient,"%")
    GROUP BY ingredients.ingredient
    HAVING c = (SELECT count(*) FROM kitchen);

当我单独运行select部分时，我得到：

作为c列的结果，我收到一个错误，因为它与insert into函数中的字段数冲突。
如何使select查询省略c列以使insert-into正常工作？我不能使列的数目相同，因为我需要count列才能使have函数工作

sql mysql Database

来源：https://stackoverflow.com/questions/49891729/how-to-omit-columns-from-select-mysql

1条答案

按热度按时间

iih3973s1#

插入时，定义了两列 (account_id, ingredient) ，而您提供了4列 kitchen.account_id, ingredients.ingredient, kitchen.ingredient, count(*) c . 只需从select中删除不想插入的列即可

INSERT INTO kitchen (account_id, ingredient) 
SELECT kitchen.account_id, ingredients.ingredient 
 FROM planned_meals JOIN meal JOIN kitchen JOIN ingredients WHERE planned_meals.mid = meal.mid AND ingredients.rid = meal.recipe_id AND ingredients.ingredient != "" AND ingredients.ingredient NOT LIKE CONCAT("%",kitchen.ingredient,"%") 
GROUP BY ingredients.ingredient 
HAVING count(*) = (SELECT count(*) FROM kitchen);

赞(0）回复(0）举报 2021-06-26

我来回答

如何从select mysql中省略列

1条答案

相关问题

热门标签

最新问答