Есть возможность создать колонку в таблице с динамическим значением?

Question

[Дмитрий Васильев]

Всем привет! Есть следующая схема таблицы:

• Problems - Проблема.
  
• Tasks - Задача.
  
• 
  Statues - Статус. Содержит 2 строки:
  
  • ID=1, Name="Completed"
    
  • ID=2, Name="Not completed"
    
  
  
  

Отношения:

• Проблемы состоят из задач (one to many)
  
• Задача имеет один статус (one to one)
  

Хотелось бы добавить поле status_id в таблицу "Проблема", которое будет считаться динамически, в зависимости от статусов задач (табл. "Задача"). Т.е.:

• Если у всех задач статус "Completed", то problems.status_id=1
  
• Если хотя бы одна задача имеет статус "Not completed", то problems.status_id=2
  
• Если у проблемы нет задач, то problems.status_id=null
  

Можете, пожалуйста, подсказать, как это можно сделать на SQL?

Answer 1

[Slava Rozhnev]

Вы можете получить статус проблемы при помощи выборки с соединением таблиц:

select 
    problems.*, 
    case when coalesce(max(status_id), 2) = 2 then 'Not Completed' else 'Completed' end problem_status
from problems
left join tasks on tasks.problem_id = problems.id
group by problems.id, problems.title
order by problems.id;

https://sqlize.online/sql/psql13/6be99ff3d4acabd6d...

Или более динамичным способом:

with problem_status as (
    select 
        problems.*, 
        max(status_id) status_id
    from problems
    left join tasks on tasks.problem_id = problems.id
    group by problems.id, problems.title
) select problem_status.id, problem_status.title, statuses.title
from problem_status
left join statuses on statuses.id = problem_status.status_id
order by problem_status.id;

https://sqlize.online/sql/psql13/946dfddb1805be86e...

Comments

[Дмитрий Васильев]

Спасибо большое!