SQLite3 : IF row exist then incriment value ELSE insert new row

Question

下午好。我尝试通过一个请求来执行这样的场景:我有一张表，每小时都会在其中输入值。如果最后一次投注的日期已经超过 1 小时，我想插入一个新条目，如果在一小时内，则增加值

示例:

+------------+-------------------+
| value      | date              |
+------------+-------------------+
|     100    |2020-08-06 12:12:12|
+------------+-------------------+
|     100    |2020-08-06 **11:11:00**|
+---------------------------------

新通知

+------------+-------------------+
|     50     |2020-08-06 **11:20:00**|
+------------+-------------------+

=>

+------------+-------------------+
| value      | date              |
+------------+-------------------+
|     100    |2020-08-06 12:12:12|
+------------+-------------------+
|     150    |2020-08-06 **11:11:00**|
+---------------------------------

需要:

IF EXISTS (SELECT * FROM energy_consumption WHERE date BETWEEN strftime('%Y-%m-%d %H:00:00', 'now') AND DATETIME('now'))
    UPDATE energy_consumption SET consumption_tarif_1 = consumption_tarif_1 + 100 WHERE date BETWEEN strftime('%Y-%m-%d %H:00:00', 'now') AND DATETIME('now')
ELSE
    INSERT INTO energy_consumption (counter_type_id, consumption_tarif_1, consumption_tarif_2, consumption_tarif_3) VALUES(1, 100, 100, 100)

但是 IF - ELSE - 不支持 SQLite 3.x

我正在尝试做电表之类的事情

Answer 1

使用 2 个语句:

UPDATE energy_consumption 
SET consumption_tarif_1 = consumption_tarif_1 + 100 
WHERE date BETWEEN strftime('%Y-%m-%d %H:00:00', 'now') AND DATETIME('now')
AND EXISTS (
  SELECT * FROM energy_consumption 
  WHERE date BETWEEN strftime('%Y-%m-%d %H:00:00', 'now') AND DATETIME('now')
);

INSERT INTO energy_consumption (counter_type_id, consumption_tarif_1, consumption_tarif_2, consumption_tarif_3) 
SELECT 1, 100, 100, 100
WHERE NOT EXISTS (
  SELECT * FROM energy_consumption 
  WHERE date BETWEEN strftime('%Y-%m-%d %H:00:00', 'now') AND DATETIME('now')
);

只有其中 1 个将被执行，因为它们都包含与 EXISTS 互斥的条件。和 NOT EXISTS .