sql - 来自 4 个不同表的数据透视表

Question

我有四个表，每个表有 2 列，其中一个列名为 common “Workflow” tb1

Workflow    |   progress
-----------------------
export data |   3

t2

Workflow    |   closed
----------------------
import data |    4

t3

Workflow    |   Finished
----------------------
extra data  |   2

t4

Workflow    |   notyet
----------------------
Oracle tags |   7

现在我正在寻找如下结果，

Workflow   |  Progress   | Closed  |  Finished   |  notyet  |
-------------------------------------------------------------
export data |  3         | 0       |  0          |  0       |
-------------------------------------------------------------
import data |  0         | 4       |  0          |  0       |
-------------------------------------------------------------
extra data  |  0         | 0       |  2          |  0       |
-------------------------------------------------------------
Oracle tags |  0         | 0       |  0          |  7       |

标签： sqltsql

Answer 1

这样做的基本方法是

select * from 
(
    select workflow, progress, 0 as closed, 0 as finished, 0 as notyet from tb1
    union all
    select workflow, 0, closed, 0,0 from tb2
    union all
    select workflow, 0, 0, finished, 0 from tb3
    union all
    select workflow, 0, 0, 0, notyet from tb4
) t1

如果您需要分组结果，则：

select 
  workflow, 
  sum(progress) as progress, 
  sum(closed) as closed, 
  sum(finished) as finished, sum(notyet) as notyet 
from 
(
    select workflow, progress, 0 as closed, 0 as finished, 0 as notyet from tb1
    union all
    select workflow, 0, closed, 0,0 from tb2
    union all
    select workflow, 0, 0, finished, 0 from tb3
    union all
    select workflow, 0, 0, 0, notyet from tb4
) t1 
group by workflow;