SQL从3个具有相同列名的表中选择

Question

我有三个表，结构如下：

sequence
cli
datetime
company

这些表是orders_broadband orders_lines和orders_porting

我希望在同一结果集中显示所有三个表的结果。

我已尝试运行该查询：

SELECT orders_broadband.*, orders_lines.*, orders_porting.* from orders_broadband, orders_lines, orders_porting

但是我想我的查询不正确

Answer 1

您可以这样做：

select sequence, cli, datetime, company from orders_broadband
union all
select sequence, cli, datetime, company from orders_lines
union all
select sequence, cli, datetime, company from orders_porting

这将合并来自所有3个表的数据。

通过添加列名explicity进行编辑(类似于xQbert在注释部分中提到的内容)

显示表名

select sequence, cli, datetime, company, 'Tablename1' as tbl from orders_broadband where status = 'New' 
UNION ALL 
select sequence, cli, datetime, company, 'Tablename2' as tbl from orders_lines where status = 'New' 
UNION ALL 
select sequence, cli, datetime, company, 'Tablename3' as tbl from orders_porting where status = 'New'

将“Tablename1”和其他代码更改为您觉得舒服的任何内容。

如果我想从orders_broadband中添加额外的列，该怎么办？

select '' as telephone_number, sequence, cli, datetime, company, 'Tablename1' as tbl from orders_broadband where status = 'New' 
UNION ALL 
select '' as telephone_number, sequence, cli, datetime, company, 'Tablename2' as tbl from orders_lines where status = 'New' 
UNION ALL 
select telephone_number, sequence, cli, datetime, company, 'Tablename3' as tbl from orders_porting where status = 'New'