How to return multiple result in the same query?

问题: 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 greather than 7 days from the current date, the...

问题:

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 greather 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, infact the result above is: 67, 68, becasue 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?


回答1:

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.


回答2:

Use another table with IDs and then do a left join between them.

  • 发表于 2019-03-20 19:41
  • 阅读 ( 179 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除