mysql - 使用mysql上的 View 组合来自多个表的数据

Question

我有两个表Employee和Customer。

我想要在一个 View 中显示员工的姓名和地址，以及客户的姓名和地址。

这就是我所拥有的:

CREATE VIEW Mail_List AS
SELECT C.CustName, C.CustAddress
FROM Customers C
UNION
Select E.EmpCustName, E.EmpCustAddress
From Employees E;

但它说没有行受到影响。请帮忙!

Answer 1

没有行受到影响 不是 SELECT 操作应该出现的错误。

你如何调用 View ？尝试这样做:

SELECT * FROM Mail_List;

为了清楚起见，您可以将 View 重写为:

CREATE OR REPLACE VIEW Mail_List AS
SELECT C.CustName AS name, C.CustAddress AS address, 'customer' AS `type`
FROM Customers C
UNION ALL
SELECT E.EmpCustName AS name, E.EmpCustAddress AS address, 'employee' AS `type`
From Employees E
ORDER BY name;