向视图添加额外的列，该列不存在于表中 [英] Adding extra column to view, which is not present in table

问题描述

我想用三个表的联合创建视图.但是在结果查询中，我想要一个额外的列，例如"tableId".

I want to create view with union of three tables. But in result query I want one extra column like 'tableId'.

我的代码就像

CREATE OR REPLACE VIEW DETAILS
AS SELECT 
* FROM
(
SELECT 
    T1.ID,
    T1.AMOUNT,
    T1.STATUS,
    T1.ADDEDBY,
    T1.ADDEDON
FROM Table1 T1
UNION ALL
SELECT 
    T2.ID,
    T2.AMOUNT,
    T2.STATUS,
    T2.ADDEDBY,
    T2.ADDEDON
FROM Table2 T2
UNION ALL
SELECT
    T3.ID,
    T3.BILLAMOUNT,
    T3.STATUS,
    T3.ADDEDBY,
    T3.ADDEDON  
FROM Table3 T3
);

这给了我所需的三个表的并集. 但是，如何获取结果输出中的表ID列?该列在三个表中都不存在.

This gives me union of required three tables. But how can i get table Id column in resulted output? This column is not present in any of the three tables.

推荐答案

听起来您只是想向SELECT列表中添加一个附加的硬编码值

It sounds like you just want to add an additional hard-coded value to your SELECT list

CREATE OR REPLACE VIEW DETAILS
AS SELECT 
* FROM
(
SELECT 
    T1.ID,
    T1.AMOUNT,
    T1.STATUS,
    T1.ADDEDBY,
    T1.ADDEDON,
    'T1' tableID
FROM Table1 T1
UNION ALL
SELECT 
    T2.ID,
    T2.AMOUNT,
    T2.STATUS,
    T2.ADDEDBY,
    T2.ADDEDON,
    'T2' tableID
FROM Table2 T2
UNION ALL
SELECT
    T3.ID,
    T3.BILLAMOUNT,
    T3.STATUS,
    T3.ADDEDBY,
    T3.ADDEDON,
    'T3' tableID  
FROM Table3 T3
);

这篇关于向视图添加额外的列，该列不存在于表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！