mysql - 如果最后一行的字段满足子句或子查询结果等于0则插入

Question

我正在尝试获取应用以下内容的单个插入

______________________________
| id | item (fk) | condition |
| 1  | 3         | NEW       |
| 2  | 3         | USED      |
| 3  | 2         | USED      |
______________________________

我要实现的目标如下:

例如，给定一个项目 id = 3，如果满足以下两个条件之一，我想在此表中插入一个新行:

1. id 最大的行不是“NEW”`
2. 此项 (id = 3) 的条目计数 等于 0

到目前为止我得到的最接近的是:

INSERT INTO tableA (item, condition)
        SELECT (itemId, "NEW")  FROM ( 
                    SELECT condition, count(*) as rowCount
                    FROM tableA 
                    WHERE item = itemId 
                    ORDER BY id DESC LIMIT 1 ) a
            WHERE a.condition <> "NEW" OR a.rowCount = 0;

但是 count(*) 弄乱了顺序。有什么线索吗？

Answer 1

我将其视为没有 GROUP BY 的聚合子查询。这总是只返回一行，所以即使 cnt 是 0，你仍然会有一行:

INSERT INTO tableA (item, condition)
    SELECT a.itemId, a.condition
    FROM (SELECT @itemId as itemId, 'NEW' as condition,
                 COUNT(*) as cnt,
                 MAX(id) as max_id,
                 MAX(CASE WHEN condition = 'NEW' THEN id END) as max_new
          FROM tableA
          WHERE item = @itemId
         ) a
    WHERE cnt = 0 OR max_new is null or max_new < max_id;