创建后视图以组合多个表数据

Question

我有多个具有不同列名称的表，如下，

客户表： c_id， customer_name， phone_number， postal_code

员工表：员工_name，e_id，mobile_no，zip_code

学生表：s_id，student_name，post_code

我需要创建视图来组合诸如下面的

用户表<>用户表<> /strong>：userId（c_id，e_id，s_id），用户名（所有名称列），zip_code，contact

示例数据

客户表：

|  c_id  |  Company_Name  |  Phone |
|----------------------------------|
|  1     |  Company 1     | *******|
|----------------------------------|

员工表：

|  e_id  |  Employee_Name | Mobile |
|----------------------------------|
|  1     |  employee 1    | *******|
|----------------------------------|

期望查看：

|  userId  |   user_Name   | contact  |
|-------------------------------------|
|  c_1     |   Company 1   |  ******* |
|-------------------------------------|
|  e_1     |   employee 1  |  ******* |
|-------------------------------------|

如何创建视图，请帮助我，提前致谢

Answer 1

只需使用Union或联合所有根据重复值。基于数据

CREATE VIEW view_name
AS
  SELECT concat('c_',c_id)         AS userId,
         company_name AS user_Name,
         phone        AS contact
  FROM   customer
  UNION
  SELECT concat('e_',e_id),
         employee_name,
         mobile
  FROM   employee   

a href =“ https://dbfiddle.uk/?rdbms = postgres_10&； fiddle = 88f7473090fc386cdc666666dfbd30c37111”

<

CREATE VIEW view_name
AS
  SELECT c_id          AS userId,
         customer_name AS userName,
         postal_code   AS zip_code,
         phone_number  AS contact
  FROM   customer
  UNION
  SELECT e_id,
         employee_name,
         zip_code,
         mobile_no
  FROM   employee
  UNION
  SELECT s_id,
         student_name,
         post_code,
         NULL
  FROM   student