忽略插入如果值为null
所以我有查询,像这样:忽略插入如果值为null
INSERT INTO price_hist
(
date,
product_id,
price_min,
price_max,
price_avg,
merchants
)
VALUES
(
'2015-07-10',
100388,
(
SELECT min(price)
FROM prices
WHERE product_id = 100388
AND active = 1),
(
SELECT max(price)
FROM prices
WHERE product_id = 100388
AND active = 1),
(
SELECT avg(price)
FROM prices
WHERE product_id = 100388
AND active = 1),
(
SELECT count(price)
FROM prices
WHERE product_id = 100388
AND active = 1)
)
price_max
,price_min
和price_avg
不能为空,但并不是所有产品都涨价了,所以很自然的子查询这些产品返回NULL。如果我使用INSERT IGNORE
,查询确实执行,但不是跳过插入,而是将0值插入所有这些字段。任何方式,我可以修改它,所以它会一起跳过插入,如果任何字段为空?
product_history
表已经有设置的字段,所以它们不能为NULL。
只需使用一个INSERT ... SELECT
,而不是:你的子查询的查询
INSERT INTO price_hist
(
date,
product_id,
price_min,
price_max,
price_avg,
merchants
)
SELECT
'2015-07-10',
100388,
min(price),
max(price),
avg(price),
count(price)
FROM prices
WHERE product_id = 100388
AND active = 1
HAVING count(price)
美丽!正是我在找什么!谢谢! – galdikas
使用IFNULL()
试试这个
INSERT INTO price_hist
(
date,
product_id,
price_min,
price_max,
price_avg,
merchants
)
VALUES
(
'2015-07-10',
100388,
(
IFNULL(SELECT min(price)
FROM prices
WHERE product_id = 100388
AND active = 1), 0),
(
IFNULL(SELECT max(price)
FROM prices
WHERE product_id = 100388
AND active = 1), 0),
(
IFNULL(SELECT avg(price)
FROM prices
WHERE product_id = 100388
AND active = 1), 0),
(
IFNULL(SELECT count(price)
FROM prices
WHERE product_id = 100388
AND active = 1), 0)
)
如果我理解正确,这将返回0?然后将继续并将其插入历史表中? – galdikas
是因为你说product_history表已经有设置的字段,所以它们不能是'NULL' –
INSERT INTO price_hist
(
date,
product_id,
price_min,
price_max,
price_avg,
merchants
)
VALUES
(
'2015-07-10',
100388,
(
SELECT ifnull(min(price),0)
FROM prices
WHERE product_id = 100388
AND active = 1),
(
SELECT max(price)
FROM prices
WHERE product_id = 100388
AND active = 1),
(
SELECT ifnull(avg(price),0)
FROM prices
WHERE product_id = 100388
AND active = 1),
(
SELECT count(price)
FROM prices
WHERE product_id = 100388
AND active = 1)
)
这将插入0,价格历史,我想跳过插入,如果任何值为NULL – galdikas
你试过IFNULL()盈? – Markus