如何在同一查询中返回多个结果? [英] How to return multiple result in the same query?

问题描述

我写了一个查询，检查team是否已更新.检查更新的条件是:如果team的字段update_at大于当前日期，则需要更新记录，例如:

id | name     | update_at
67   Tirana     2019-03-06 11:00:12  
68   Partizan   2019-03-06 11:02:04  
69   Lusitanos  2019-03-14 09:00:40

SELECT id
FROM team
WHERE update_at < DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY)
  AND id IN (67, 68, 69);

查询效果很好，实际上上面的结果是:67、68，因为记录69已被更新(不符合条件).

现在假设我要查找的记录在数据库中不存在，查询将不会返回它(这很好)，但是我如何检查是否需要添加记录而不是更新记录?例如:

id | name     | update_at
67   Tirana     2019-03-06 11:00:12  
68   Partizan   2019-03-06 11:02:04  
69   Lusitanos  2019-03-14 09:00:40

SELECT id
FROM team
WHERE update_at < DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY)
AND id IN (67, 68, 69, 70);

结果甚至是67、68.但是与第一个示例相反，这里的记录70不存在，所以我如何使用一个查询来知道呢?

是否可能返回两个结果，例如record_to_update和record_to_add?

解决方案

您需要使用派生表或类似逻辑的LEFT JOIN.例如:

SELECT tt.team_id
FROM (SELECT 67 as team_id UNION ALL SELECT 68 UNION ALL
      SELECT 69 UNION ALL SELECT 70
     ) tt LEFT JOIN
     team t
     on t.id = tt.team_id AND
        update_at >= DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY)
WHERE t.id IS NULL;

此处是db 小提琴.

如果要检查update与insert，可以执行以下操作:

SELECT tt.team_id,
       (CASE WHEN t.id IS NULL THEN 'INSERT'
             ELSE 'UPDATE'
        END) as what_to_do
FROM (SELECT 67 as team_id UNION ALL SELECT 68 UNION ALL
      SELECT 69 UNION ALL SELECT 70
     ) tt LEFT JOIN
     team t
     on t.id = tt.team_id 
WHERE t.id IS NULL OR 
      t.update_at < DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY);

此处是相应的db< fiddle. >

顺便说一句，这是使用LEFT JOIN的一种有趣情况，但是对第二个表的过滤是在WHERE子句中，而不是在ON子句中.

I wrote a query that check if a team is updated. The condition to check the update is: if the field update_at of the team is greater than 7 days from the current date, then the record need to be updated, eg:

id | name     | update_at
67   Tirana     2019-03-06 11:00:12  
68   Partizan   2019-03-06 11:02:04  
69   Lusitanos  2019-03-14 09:00:40

SELECT id
FROM team
WHERE update_at < DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY)
  AND id IN (67, 68, 69);

The query works well, in fact the result above is: 67, 68, because the record 69 is already updated (doesn't fit the condition).

Now suppose that the record that I'm looking for doesn't exist in the database, the query will not return it (and this is good), but how can I check if the record need to be added instead of update? eg:

id | name     | update_at
67   Tirana     2019-03-06 11:00:12  
68   Partizan   2019-03-06 11:02:04  
69   Lusitanos  2019-03-14 09:00:40

SELECT id
FROM team
WHERE update_at < DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY)
AND id IN (67, 68, 69, 70);

the result is even 67, 68. But contrary to the first example, here the record 70 doesn't exist, so how can I know that using one query?

Is possible return two result such as record_to_update and record_to_add?

解决方案

You need a LEFT JOIN using a derived table or similar logic. For instance:

SELECT tt.team_id
FROM (SELECT 67 as team_id UNION ALL SELECT 68 UNION ALL
      SELECT 69 UNION ALL SELECT 70
     ) tt LEFT JOIN
     team t
     on t.id = tt.team_id AND
        update_at >= DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY)
WHERE t.id IS NULL;

Here is a db<>fiddle.

EDIT:

If you want to check update versus insert, you can do:

SELECT tt.team_id,
       (CASE WHEN t.id IS NULL THEN 'INSERT'
             ELSE 'UPDATE'
        END) as what_to_do
FROM (SELECT 67 as team_id UNION ALL SELECT 68 UNION ALL
      SELECT 69 UNION ALL SELECT 70
     ) tt LEFT JOIN
     team t
     on t.id = tt.team_id 
WHERE t.id IS NULL OR 
      t.update_at < DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY);

Here is the corresponding db<>fiddle.

By the way, this is an interesting case where LEFT JOIN is used, but the filtering on the second table is in the WHERE clause, not the ON clause.

这篇关于如何在同一查询中返回多个结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！