如何从两个表的两个不同列插入一个列？ [英] How to insert into one column from two table's two different column?

问题描述

我有三张桌子。

我想使用一个 SQL语句在table3.stat中插入以下值。

1.从table0，table2中选择状态，其中table0.serial = table2.num

2.从table1，table2中选择条件，其中table1.ID = table2.num

可以用postgresql吗？

I got three tables.
I want to insert below value to table3.stat with "one" sql statement.
1.select status from table0, table2 where table0.serial=table2.num
2.select condition from table1, table2 where table1.ID=table2.num
Is it possible with postgresql?

table0:
| serial | status |   |
|--------|--------|---|
| a22    | good   |   |
| a33    | bad    |   |
| a11    | bad    |   |

table1:
| ID  | condition |   |
|-----|-----------|---|
| a00 | awesome   |   |
| a44 | bad       |   |
| a11 | bad       |   |

table2:
| num  |   |   |
|------|---|---|
| a00  |   |   |
| a44  |   |   |
| a22  |   |   |
| a33  |   |   |


table3:
| num | stat |
|-----|------|
|     |      |
| -   | -    |

desired result:
| num | stat  |
|-----|-------|
| a22 | good  |
| a33 | bad   |
| a00 |awesome|
| a44 |  bad  |

推荐答案

SQL DEMO

SQL DEMO

使用 UNION ALL 即使是两个选择，也只是一个语句

Use UNION ALL even when are two select, is just one statement

INSERT INTO table3 ("num", "stat")
select table2."num", table0."status" 
from table0
JOIN table2 ON table0."serial"=table2."num"

UNION ALL

select table2."num", table1."condition" 
from table1
JOIN table2 ON table1."ID"=table2."num"

; 

这篇关于如何从两个表的两个不同列插入一个列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！