使用UNION创建MySQL视图

Question

我试图为下面的查询创建一个视图。

SELECT DISTINCT
  products.pid        AS id,
  products.pname      AS name,
  products.p_desc     AS description,
  products.p_loc      AS location,
  products.p_uid      AS userid,
  products.isaproduct AS whatisit
FROM products
UNION
SELECT DISTINCT
  services.s_id       AS id,
  services.s_name     AS name,
  services.s_desc     AS description,
  services.s_uid      AS userid,
  services.s_location AS location,
  services.isaservice AS whatisit
FROM services

但却无法做到。我正在使用MySql查询浏览器。我得到的错误是：

只能从SELECT命令的活动结果集创建视图。

有人能帮我吗？

Answer 1

CREATE VIEW vw_product_services AS
SELECT DISTINCT products.pid AS id,
                products.pname AS name,
                products.p_desc AS description,
                products.p_loc AS location,
                products.p_uid AS userid,
                products.isaproduct AS whatisit
           FROM products
          UNION
          SELECT DISTINCT services.s_id AS id,
                services.s_name AS name,
                services.s_desc AS description,
                services.s_uid AS userid,
                services.s_location AS location,
                services.isaservice AS whatisit
           FROM services

我试过了而且成功了！谢谢大家:)

Answer 2

您可能希望在第二个选择中使用userid和位置的顺序。在联合的所有选择中，列名应该匹配1到1。

编辑:对于查询浏览器，正如这所指出的，“要从查询中创建视图，您必须已经成功地执行了查询。更准确地说，视图是从最新成功执行的查询创建的，而不一定是从查询区域当前的查询创建的。”

因此，在查询浏览器中创建视图之前，需要先执行查询。

错误来自查询浏览器，而不是mysql。

Answer 3

将不同的类型合并到同一列中。(名称可能不同，但类型必须相同，或者至少是自动浇注的。)但是，正如@Learning指出的那样，您似乎扭曲了SELECT列枚举。

以防万一，正确的语法(对我起作用)是

CREATE VIEW myView 
AS  
SELECT ...