我想用三个表的联合创建视图.但在结果查询中,我想要一个额外的列,如’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列？此列不存在于三个表中的任何一个中.
解决方法
听起来你只想在SELECT列表中添加一个额外的硬编码值
CREATE OR REPLACE VIEW DETAILS
AS SELECT
* FROM
(
SELECT
T1.ID,T1.ADDEDON,'T1' tableID
FROM Table1 T1
UNION ALL
SELECT
T2.ID,T2.ADDEDON,'T2' tableID
FROM Table2 T2
UNION ALL
SELECT
T3.ID,T3.ADDEDON,'T3' tableID
FROM Table3 T3
);
