sqlite - SQLite使用来自不同表的总计创建视图(Sqlite create view with totals from different tables)

Question

I have 3 tables with identique structure as follow dpt1_taks, dpt2_tasks and dpt3_tasks.

(我有3个具有identique结构的表，如下dpt1_taks，dpt2_tasks和dpt3_tasks。)

This is the structure of each one (4 columns):

(这是每一个的结构（4列）：)

id | task_date | assigned | done
_________________________________
1  |01-11-2019 |     x    |  x
2  |01-11-2019 |     x    |     
3  |01-11-2019 |     x    |  x
1  |02-11-2019 |          |
1  |02-11-2019 |     x    |  x
1  |03-11-2019 |     x    |
1  |03-11-2019 |          |
1  |03-11-2019 |     x    |
1  |04-11-2019 |     x    |  x

How can I create an sqlite view which groups the count per date of each table as follow:

(如何创建一个sqlite视图，将每个表的每个日期的计数分组如下：)

DATE      | dpt1_assigned | dpt1_done | dpt2_assigned | dpt2_done |dpt3_assigned | dpt3_done |
_______________________________________________________________________________________________
01-11-2019|       3       |      2    |        5      |      3    |      4       |    0      |
02-11-2019|       1       |      1    |        5      |      3    |      4       |    0      |

Thank you in advance for any help and best regards.

(在此先感谢您的帮助和问候。)

  ask by karmel translate from so

Answer 1

The results for each table must be calculated separately and then joined by date:

(每个表的结果必须分别计算，然后按日期合并：)

with 
  dates as (
    select task_date from dpt1_taks union  
    select task_date from dpt2_taks union
    select task_date from dpt3_taks 
  ),  
  cte1 as (
    select task_date,
      coalesce(sum(assigned = 'x'), 0) dpt_assigned,
      coalesce(sum(done = 'x'), 0) dpt_done
    from dpt1_taks
    group by task_date
  ),  
  cte2 as (
    select task_date,
      coalesce(sum(assigned = 'x'), 0) dpt_assigned,
      coalesce(sum(done = 'x'), 0) dpt_done
    from dpt2_taks
    group by task_date
  ),
  cte3 as (
    select task_date,
      coalesce(sum(assigned = 'x'), 0) dpt_assigned,
      coalesce(sum(done = 'x'), 0) dpt_done
    from dpt3_taks
    group by task_date
  )

select d.task_date,
  c1.dpt_assigned dpt_assigned1, c1.dpt_done dpt_done1,
  c2.dpt_assigned dpt_assigned2, c2.dpt_done dpt_done2,
  c3.dpt_assigned dpt_assigned3, c3.dpt_done dpt_done3
from dates d
left join cte1 c1 on c1.task_date = d.task_date
left join cte2 c2 on c2.task_date = d.task_date
left join cte3 c3 on c3.task_date = d.task_date

See the demo .

(参见演示 。)