如何使用 CASE..WHEN 检查子查询结果? [英] How can I use CASE..WHEN to check subquery result?

问题描述

这是我的查询:

INSERT INTO mytable(questin_id)
SELECT t1.id
FROM qanda t1 
WHERE (EXISTS (SELECT 1 
               FROM qanda t2 
               WHERE ID = :id 
                 and t1.ID = t2.related)
           or t1.id = :id)
  and Type = 0;

SELECT 语句返回一个 id 并将其插入到 mytable 中.一切都很好.但有时 WHERE 子句上的条件不匹配任何内容并且 SELECT 语句不返回任何内容，因此不会插入任何内容.虽然我总是需要插入一个新行.所以我想检查 SELECT 语句是否匹配任何内容，然后它应该返回 null.这是我的新查询:

The SELECT statement returns an id and it will be inserted into mytable. All fine. But sometimes the conditions on WHERE clause does't match anything and the SELECT statement returns nothing, so nothing will be inserted. While I always need a new row to be inserted. So I want to check if the SELECT statement matched nothing, then it should return null. Here is my new query:

INSERT INTO mytable(questin_id)
SELECT CASE WHEN EXISTS(t1.id) THEN t1.id ELSE NULL END
FROM qanda t1 
WHERE (EXISTS (SELECT 1 
               FROM qanda t2 
               WHERE ID = :id 
                 and t1.ID = t2.related)
           or t1.id = :id)
  and Type = 0;

但它没有按预期工作.有什么想法吗?

But it doesn't work as expected. Any idea?

推荐答案

如果你总是想插入1个值，那么你可以使用聚合:

If you always want to insert 1 value, then you can use aggregation:

INSERT INTO mytable(questin_id)
    SELECT MAX(t1.id)
    FROM qanda t1 
    WHERE (EXISTS (SELECT 1 
                   FROM qanda t2 
                   WHERE t2.ID = :id AND
                         t1.ID = t2.related
                  ) OR
           t1.id = :id
          ) AND
          Type = 0;

当然，这只会返回一行，即使逻辑(除了MAX())会返回多于一行.

Of course, this will return only one row, even if the logic (apart from the MAX()) would return more than one row.

这篇关于如何使用 CASE..WHEN 检查子查询结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！